Oracle行列转换

列转行：Oracle 12后不可用

• WM_CONCAT（fieldname）/WMSYS.WM_CONCAT（fieldname）

配合group by使用。

eg:

SELECT training_dt, WMSYS.WM_CONCAT(employee_ID) FROM TraningTable GROUP BY training_dt；

• listagg(fieldname1) within group(order by fieldname2) over(partition by fieldname3)：

SELECT listagg(str) within

GROUP(

ORDER BY ord)

FROM (SELECT rownum ord, substr('测试reverse', LEVEL * -1, 1) str

FROM dual

CONNECT BY LEVEL <= length('测试reverse'));

• unpivot子句

unpivot子句的作用是将列转换为行。

现有表格数据如下：

select * from pivot_sales_data unpivot( amount for month in (jan, feb, mar, apr) ) order by prd_type_id;

----------------------------------------------------------------行转列 分割线----------------------------------------------------------------

行转列pivot：聚合

select * from ( select month, prd_type_id, amount from all_sales where year = 2003 and prd_type_id in(1, 2, 3) ) pivot( sum(amount) for month in (1 as JAN, 2 as FEB, 3 as MAR， 4 as APR) ) order by prd_type_id;

select * from ( select month, prd_type_id, amount from all_sales where year = 2003 and prd_type_id in(1,2,3) ) pivot ( sum(amount) for(month, prd_type_id) in( (1, 1) as jan_prd_type_1, (2, 2) as feb_prd_type_2, (3, 3) as mar_prd_type_3, (4, 2) as apr_prd_type_2 ) );

select * from ( select month, prd_type_id, amount from all_sales where year = 2003 and prd_type_id in(1, 2, 3) ) pivot ( sum(amount) as sum_amount, avg(amount) as avg_amount for(month) in( 1 as JAN, 2 as FEB ) ) order by prd_type_id;