一、固定行列转换
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;