WHCSRL 技术网

Oracle pivot 行转列

1:数据准备

  1. create table t_sale (
  2. org varchar(10),
  3. month varchar(10),
  4. amt number
  5. )
  6. Insert into t_sale (ORG,MONTH,AMT) values ('黄陂区','01',42349.06);
  7. Insert into t_sale (ORG,MONTH,AMT) values ('黄陂区','02',63242.24);
  8. Insert into t_sale (ORG,MONTH,AMT) values ('黄陂区','03',76381.24);
  9. Insert into t_sale (ORG,MONTH,AMT) values ('黄陂区','04',77306.82);
  10. Insert into t_sale (ORG,MONTH,AMT) values ('黄陂区','05',62114.31);
  11. Insert into t_sale (ORG,MONTH,AMT) values ('黄陂区','06',54982.29);
  12. Insert into t_sale (ORG,MONTH,AMT) values ('黄陂区','07',31933.97);
  13. Insert into t_sale (ORG,MONTH,AMT) values ('黄陂区','08',31387.07);
  14. Insert into t_sale (ORG,MONTH,AMT) values ('江岸区','01',12349.06);
  15. Insert into t_sale (ORG,MONTH,AMT) values ('江岸区','02',33242.24);
  16. Insert into t_sale (ORG,MONTH,AMT) values ('江岸区','03',16381.44);
  17. Insert into t_sale (ORG,MONTH,AMT) values ('江岸区','04',27306.8);
  18. Insert into t_sale (ORG,MONTH,AMT) values ('江岸区','05',12114.31);
  19. Insert into t_sale (ORG,MONTH,AMT) values ('江岸区','06',34982.2);
  20. Insert into t_sale (ORG,MONTH,AMT) values ('江岸区','07',11933.97);
  21. Insert into t_sale (ORG,MONTH,AMT) values ('江岸区','08',6087.17);
  22. Insert into t_sale (ORG,MONTH,AMT) values ('武昌区','01',4129.06);
  23. Insert into t_sale (ORG,MONTH,AMT) values ('武昌区','02',61242.74);
  24. Insert into t_sale (ORG,MONTH,AMT) values ('武昌区','03',7081.44);
  25. Insert into t_sale (ORG,MONTH,AMT) values ('武昌区','04',62306.8);
  26. Insert into t_sale (ORG,MONTH,AMT) values ('武昌区','05',1114.61);
  27. Insert into t_sale (ORG,MONTH,AMT) values ('武昌区','06',50082.2);
  28. Insert into t_sale (ORG,MONTH,AMT) values ('武昌区','07',30133.97);
  29. Insert into t_sale (ORG,MONTH,AMT) values ('武昌区','08',31387.77);
  30. Insert into t_sale (ORG,MONTH,AMT) values ('黄陂区','09',40589.48);
  31. Insert into t_sale (ORG,MONTH,AMT) values ('江岸区','09',92668.18);
  32. Insert into t_sale (ORG,MONTH,AMT) values ('江岸区','10',293949.36);
  33. Insert into t_sale (ORG,MONTH,AMT) values ('黄陂区','10',28268.83);
  34. Insert into t_sale (ORG,MONTH,AMT) values ('武昌区','10',896.81);

结果显示:

 

2: pivot函数语法

  1. SELECT <non-pivoted column>,
  2. [first pivoted column] AS <column name>,
  3. [second pivoted column] AS <column name>,
  4. ...
  5. [last pivoted column] AS <column name>
  6. FROM
  7. (<SELECT query that produces the data>)
  8. AS <alias for the source query>
  9. PIVOT
  10. (
  11. <aggregation function>(<column being aggregated>)
  12. FOR
  13. [<column that contains the values that will become column headers>]
  14. IN ( [first pivoted column], [second pivoted column],
  15. ... [last pivoted column])
  16. ) AS <alias for the pivot table>
  17. <optional ORDER BY clause>;

 其中:

<aggregation function>(<column being aggregated>)  

聚合函数:需要告诉pivot函数进行转列的过程中,聚合操作的函数和处理对象;

FOR

[<column that contains the values that will become column headers>]  行转列的标准

 IN ( [first pivoted column], [second pivoted column],   行转列取值
    ... [last pivoted column])  

行转列标准:根据那一列的值进行行转列:

列转行取值:因为要将数据行取值转成列,我们需要告诉Oracle那些取值成列,并且这些取值成列的过程中,列顺序是如何的;此示例根据t_sale种month 列的值进行行转列

如:01 1月, 02  2月,03 3月......

代码片段应该如下:

  1. PIVOT
  2. (
  3. sum(AMT)
  4. FOR month IN ('01' as Jan,'02' as Feb,'03' as Mar,
  5. '04' as Apr,'05' as May,'06' as Jun,
  6. '07' as Jul,'08' as Aug,'09' as Sep,
  7. '10' as Oct,'11' as Nov,'12' as Dec)
  8. )

3:SQL实现如下:

  1. select * from (
  2. select org,month,amt from t_sale)
  3. pivot
  4. (
  5. sum(amt)
  6. for month in ('01' as jan,'02' as feb,'03' as mar,
  7. '04' as apr,'05' as may,'06' as jun,
  8. '07' as jul,'08' as aug,'09' as sep,
  9. '10' as oct,'11' as nov,'12' as dec)
  10. )

结果显示如下:

 

 

推荐阅读