Oracle 10g 中的透视/交叉表查询(动态列号)

2022-01-22 00:00:00 pivot sql oracle transformation

我有这个表格视图

UserName      Product     NumberPurchaces
--------      -------     ---------------
'John Doe'    'Chair'     4
'John Doe'    'Table'     1
'Jane Doe'    'Table'     2
'Jane Doe'    'Bed'       1

如何在 Oracle 10g 中创建一个提供此数据透视视图的查询?

How can I create a query that will provide this pivot view in Oracle 10g ?

 UserName   Chair   Table   Bed
 --------   -----   -----   ---
 John Doe   4       1       0
 Jane Doe   0       2       1

有什么方法可以动态地做到这一点?我看到了很多方法(解码、PL/SQL 循环、联合、11g Pivot)

Any way to do it dynamically? I saw so many approaches (decode, PL/SQL loops, unions, 11g pivot)

但是根据上面的例子，我还没有找到适合我的东西

But I've yet to find something that will work for me based on the above example

编辑:我不知道开发时产品的数量或类型，所以这必须是动态的

Edit: I don't know the number or type of products in development time so this has to be dynamic

推荐答案

Oracle 11g 是第一个支持 PIVOT/UNPIVOT，所以你必须使用:

Oracle 11g is the first to support PIVOT/UNPIVOT, so you have to use:

  SELECT t.username,
         MAX(CASE WHEN t.product = 'Chair' THEN t.numberpurchases ELSE NULL END) AS chair,
         MAX(CASE WHEN t.product = 'Table' THEN t.numberpurchases ELSE NULL END) AS tbl,
         MAX(CASE WHEN t.product = 'Bed' THEN t.numberpurchases ELSE NULL END) AS bed
    FROM TABLE t
GROUP BY t.username

您可以使用 DECODE，但从 9i 开始支持 CASE.

You could use DECODE, but CASE has been supported since 9i.

相关文章