Oracle 10g 中的透视/交叉表查询（动态列号）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3479740/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Pivot / Crosstab Query in Oracle 10g (Dynamic column number)
提问by Eran Medan
I have this table view
我有这个表格视图
UserName Product NumberPurchaces
-------- ------- ---------------
'John Doe' 'Chair' 4
'John Doe' 'Table' 1
'Jane Doe' 'Table' 2
'Jane Doe' 'Bed' 1
How can I create a query that will provide this pivot view in Oracle 10g ?
如何在 Oracle 10g 中创建将提供此透视图的查询？
UserName Chair Table Bed
-------- ----- ----- ---
John Doe 4 1 0
Jane Doe 0 2 1
Any way to do it dynamically? I saw so many approaches (decode, PL/SQL loops, unions, 11g pivot)
有什么办法可以动态地做到这一点？我看到了很多方法（解码、PL/SQL 循环、联合、11g 数据透视）
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
编辑：我不知道开发时产品的数量或类型，所以这必须是动态的
回答by OMG Ponies
Oracle 11g is the first to support PIVOT/UNPIVOT, so you have to use:
Oracle 11g 是第一个支持 PIVOT/UNPIVOT，所以你必须使用：
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
You could use DECODE, but CASE has been supported since 9i.
您可以使用 DECODE，但从 9i 开始就支持 CASE。
回答by MatBailie
I guess one would have to write some code to dynamically create the query. Each MAX() line is identical except for the 'CHAIR', 'TABLE', etc, strings.
我想必须编写一些代码来动态创建查询。除了“CHAIR”、“TABLE”等字符串外，每个 MAX() 行都是相同的。
So, one would have to itterate through the data to find all the products and build up a second query as one goes. Then execute that dynamically built query.
因此，人们将不得不遍历数据以查找所有产品并逐步建立第二个查询。然后执行该动态构建的查询。
