MS SQL Server 2005引入的pivot/unpivot关键字，对于行列转换确实非常的方便。所以Oracle11g也引入了这两个关键字，语法和SQL Server 2005的基本上差不错。
SQL> select * from sales;
PRODCUT QUARTER AMOUNT
---------- ---------- ----------
a 1 100
a 2 110
a 3 130
a 4 140
b 1 210
b 2 220
b 3 230
b 4 240
PRODCUT QUARTER AMOUNT
---------- ---------- ----------
a 1 100
a 2 110
a 3 130
a 4 140
b 1 210
b 2 220
b 3 230
b 4 240
使用pivot转换显示：
SQL> select * from sales
2 pivot (sum(amount)
3 for quarter in
4 (1 as Q1,2 as Q2,3 as Q3,4 as Q4));
PRODCUT Q1 Q2 Q3 Q4
---------- ---------- ---------- ---------- ----------
a 100 110 130 140
b 210 220 230 240
2 pivot (sum(amount)
3 for quarter in
4 (1 as Q1,2 as Q2,3 as Q3,4 as Q4));
PRODCUT Q1 Q2 Q3 Q4
---------- ---------- ---------- ---------- ----------
a 100 110 130 140
b 210 220 230 240
Unpivot则执行和pivot相反的转换
SQL> create table sales2
2 as
3 select * from sales
4 pivot (sum(amount)
5 for quarter in
6 (1 as Q1,2 as Q2,3 as Q3,4 as Q4));
Table created.
SQL> select * from sales2;
PRODCUT Q1 Q2 Q3 Q4
---------- ---------- ---------- ---------- ----------
a 100 110 130 140
b 210 220 230 240
2 as
3 select * from sales
4 pivot (sum(amount)
5 for quarter in
6 (1 as Q1,2 as Q2,3 as Q3,4 as Q4));
Table created.
SQL> select * from sales2;
PRODCUT Q1 Q2 Q3 Q4
---------- ---------- ---------- ---------- ----------
a 100 110 130 140
b 210 220 230 240
SQL> select * from sales2
2 unpivot
3 (amount
4 for quarter in (Q1 as 1,Q2 as 2,Q3 as 3,Q4 as 4));
PRODCUT QUARTER AMOUNT
---------- ---------- ----------
a 1 100
a 2 110
a 3 130
a 4 140
b 1 210
b 2 220
b 3 230
b 4 240
2 unpivot
3 (amount
4 for quarter in (Q1 as 1,Q2 as 2,Q3 as 3,Q4 as 4));
PRODCUT QUARTER AMOUNT
---------- ---------- ----------
a 1 100
a 2 110
a 3 130
a 4 140
b 1 210
b 2 220
b 3 230
b 4 240
上述的例子都是根据一个列进行一次聚合的转换，实际上也可以根据多列进行多次聚合，更详细的语法，请参考Oracle官方文档。
wordpress的插件:coolcode
SQL 关键字 加亮显示 不错,红红绿绿的, 咋整的?