问题描述
我有这个表格视图
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.
这篇关于Oracle 10g 中的透视/交叉表查询(动态列号)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！