使用SQL枢轴

Question

我使用Oracle 11gR2的窗口服务器上运行2008 R2

我有这样

year code value 
    ---- ---- ----- 
    1991  1  v1 
    1991  2  v2 
    1991  3  v3 

    1992  1  v4 
    1992  2  v5 
    1992  3  v6 

    ... 
    2050 

一个表中的代码列可以是从1到10。

我会喜欢看最后的输出为（列值为1到10）

year 1 2 3 4 .... 10 
---- -- -- -- --  -- 
1991 v1 v2 v3 null.......null 
1992 v4 v5 v6. ...etc 

我该如何做pivoting来得到上述格式的结果？提前致谢。

Answer 1

不使用PIVOT一个简单的方法是使用CASE：

SELECT year, 
     MIN(CASE WHEN code = 1 THEN value END) AS Col1, 
     MIN(CASE WHEN code = 2 THEN value END) AS Col2, 
     MIN(CASE WHEN code = 3 THEN value END) AS Col3, 
     MIN(CASE WHEN code = 4 THEN value END) AS Col4, 
     MIN(CASE WHEN code = 5 THEN value END) AS Col5, 
     MIN(CASE WHEN code = 6 THEN value END) AS Col6, 
     MIN(CASE WHEN code = 7 THEN value END) AS Col7, 
     MIN(CASE WHEN code = 8 THEN value END) AS Col8, 
     MIN(CASE WHEN code = 9 THEN value END) AS Col9, 
     MIN(CASE WHEN code = 10 THEN value END) AS Col10 
FROM YourTable 
GROUP BY year 

随着PIVOT，这将是这样的：

SELECT * 
FROM (SELECT year, code, value FROM YourTable) 
PIVOT (MIN(value) FOR code IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)); 

Answer 2

的Oracle 11g具有可以用来变换一个PIVOT功能数据转换成行：

select * 
from 
(
    select year, code, value 
    from yourtable 
) 
pivot 
(
    max(value) 
    for code in ('1', '2', '3', '4', '5', '6', '7', '8', '9', '10') 
) piv 

见SQL Fiddle with Demo

Answer 3

在11g中，你可以使用pivot：

SELECT * 
FROM t 
PIVOT (max(value) as value FOR (code) IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10))