列转行：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;
