oracle如何实现行列转换

一、固定行列转换

a_table、b_table 两表数据如下图所示

            

                 a_table数据                                                                                             b_table数据

1、行转列，将a_table表的行转为列

（1）decode实现

SELECT c_year,
MAX(DECODE(c_month,'1月',c_qty)) AS 一月,
MAX(DECODE(c_month,'2月',c_qty)) AS 二月,
MAX(DECODE(c_month,'3月',c_qty)) AS 三月,
MAX(DECODE(c_month,'4月',c_qty)) AS 四月
FROM a_table WHERE c_month IN('1月','2月','3月','4月')
GROUP BY c_year;

（2）case when实现

SELECT c_year,
MAX(CASE c_month WHEN '1月' THEN c_qty END) AS 一月,
MAX(CASE c_month WHEN '2月' THEN c_qty END) AS 二月,
MAX(CASE c_month WHEN '3月' THEN c_qty END) AS 三月,
MAX(CASE c_month WHEN '4月' THEN c_qty END) AS 四月
FROM a_table WHERE c_month IN('1月','2月','3月','4月')
GROUP BY c_year;

（3）pivot函数实现

 pivot语法：pivot（任一聚合函数 for 转列值所在的列名 in（转为列名的值）），其中 in(‘’) 中可以指定别名，in中还可以指定子查询

SELECT * FROM a_table
PIVOT(SUM(c_qty) FOR c_month IN('1月' AS 一月,'2月' AS 二月,'3月' AS 三月,'4月' AS 四月));
--这里将a_table表作为pivot的输入表，一般输入表只返回需要用到的列，这里因为表字段都需要，所以可以直接用a_table表作为输入表
或
SELECT * FROM 
(SELECT c_year AS ye,c_month AS mon,c_qty AS qty FROM a_table) a 
PIVOT(SUM(qty) FOR mon IN('1月' AS 一月,'2月' AS 二月,'3月' AS 三月,'4月' AS 四月));
--这里查询返回的a表作为pivot的输入表

 

2、列转行，将b_table表的列转为行

（1）decode实现，对原表做笛卡尔积，将一行复制成4行

SELECT t_year,
DECODE(lvl,1,'1月',2,'2月',3,'3月',4,'4月') AS t_month,
DECODE(lvl,1,one_month,2,two_month,3,three_month,4,four_month) AS t_qty
FROM b_table,(SELECT LEVEL lvl FROM dual CONNECT BY LEVEL<=4)
ORDER BY t_year,t_month;

（2）case when实现，与decode同理

SELECT t_year,
CASE lvl WHEN 1 THEN '1月' 
    WHEN 2 THEN '2月' 
    WHEN 3 THEN '3月' 
    WHEN 4 THEN '4月' 
END AS t_month,
CASE lvl WHEN 1 THEN one_month 
    WHEN 2 THEN two_month 
    WHEN 3 THEN three_month 
    WHEN 4 THEN four_month 
END AS t_qty
FROM b_table,(SELECT LEVEL lvl FROM dual CONNECT BY LEVEL<=4)
ORDER BY t_year,t_month;

 

（3）unpivot函数实现

unpivot语法：unpivot(新增值所在列的列名 for 列名转为行值后所在列名 in(转为行的列名))

SELECT * FROM b_table
UNPIVOT(t_qty FOR t_month IN(ONE_MONTH AS '1月',TWO_MONTH AS '2月',THREE_MONTH AS '3月',FOUR_MONTH AS '4月'))
ORDER BY t_year,t_month;

 

二、不固定行列转换

1、多行转一列

（1）wm_concat()函数

SELECT c_year,wm_concat(c_month) FROM a_table GROUP BY c_year;

 

（2）listagg()函数

listagg语法：listagg(‘参数1’[，’参数2’]) within group(order by 字段) [over(partition by)]

参数1为字段，参数2为字段之间的连接符

SELECT c_year,listagg(c_month,',') WITHIN GROUP(ORDER BY c_month) FROM a_table GROUP BY c_year;