ORACLE - 行列转换

编写sql 时经常会遇到行列转换问题，下面记录一下我常用的几种方式。

一、列转行

一般使用 union 函数就可以了，11g 提供了新函数 unpivot（列转行） 比较简单。这里就不提及了

 

二、行转列

大多数遇到的场景都是行转列的场景

原数据列表

将相同 NAME 的 ID 组合起来 

1、使用  wm_concat 函数

-- 将 name 相同的 id 合并为一列 with temp as ( select 1 id, 'A' name from dual union all select 2 id, 'B' name from dual union all select 3 id, 'C' name from dual union all select 4 id, 'D' name from dual union all select 5 id, 'A' name from dual union all select 6 id, 'B' name from dual union all select 7 id, 'C' name from dual union all select 8 id, 'D' name from dual ) select name,wm_concat(id) ids from temp group by name; -- 默认 , 分割 --select name,replace(wm_concat(id), ',' ,';') ids from temp group by name; -- 以 ; 分割

2、使用 decode + max 函数 （先分组排序 ，再根据序号 判断聚合）

with temp as ( select 1 id, 'A' name from dual union all select 2 id, 'B' name from dual union all select 3 id, 'C' name from dual union all select 4 id, 'D' name from dual union all select 5 id, 'A' name from dual union all select 6 id, 'B' name from dual union all select 7 id, 'C' name from dual union all select 8 id, 'D' name from dual ) select t.name, max(decode(t.rn,1,t.id,null)) || max(decode(t.rn,2,','||t.id,null)) ids from( select row_number() over(partition by name order by id) rn, name, id from temp ) t group by t.name;

结果如下：

 

3、使用 decode + sum 行转列 （月行 转月列 并总计）

原数据列表

 

使用 decode sum 行传列 （月行 转月列 并总计）

--3、使用 decode sum 行传列 （月行 转月列 并总计） with temp as ( select 1 id, 2017 t_year, 6 t_month, '6000' t_amt from dual union all select 2 id, 2017 t_year, 7 t_month, '7000' t_amt from dual union all select 3 id, 2017 t_year, 8 t_month, '8000' t_amt from dual union all select 4 id, 2017 t_year, 9 t_month, '9000' t_amt from dual union all select 5 id, 2017 t_year, 10 t_month, '10000' t_amt from dual union all select 6 id, 2017 t_year, 11 t_month, '11000' t_amt from dual union all select 7 id, 2017 t_year, 12 t_month, '12000' t_amt from dual union all select 8 id, 2018 t_year, 1 t_month, '1000' t_amt from dual union all select 9 id, 2018 t_year, 2 t_month, '2000' t_amt from dual union all select 10 id, 2018 t_year, 3 t_month, '3000' t_amt from dual union all select 11 id, 2018 t_year, 4 t_month, '4000' t_amt from dual union all select 12 id, 2018 t_year, 5 t_month, '5000' t_amt from dual union all select 13 id, 2018 t_year, 6 t_month, '6000' t_amt from dual union all select 14 id, 2018 t_year, 7 t_month, '7000' t_amt from dual union all select 15 id, 2018 t_year, 8 t_month, '8000' t_amt from dual union all select 16 id, 2018 t_year, 9 t_month, '9000' t_amt from dual union all select 17 id, 2018 t_year, 10 t_month, '10000' t_amt from dual union all select 18 id, 2018 t_year, 11 t_month, '11000' t_amt from dual union all select 19 id, 2018 t_year, 12 t_month, '12000' t_amt from dual union all select 20 id, 2019 t_year, 1 t_month, '1000' t_amt from dual union all select 21 id, 2019 t_year, 2 t_month, '2000' t_amt from dual union all select 22 id, 2019 t_year, 3 t_month, '3000' t_amt from dual union all select 23 id, 2019 t_year, 4 t_month, '4000' t_amt from dual union all select 24 id, 2019 t_year, 5 t_month, '5000' t_amt from dual union all select 25 id, 2019 t_year, 6 t_month, '6000' t_amt from dual union all select 26 id, 2019 t_year, 7 t_month, '7000' t_amt from dual ) -- select * from temp order by id; select t_year, sum(decode(t_month,1,t_amt,0)) as 一月, sum(decode(t_month,2,t_amt,0)) as 二月, sum(decode(t_month,3,t_amt,0)) as 三月, sum(decode(t_month,4,t_amt,0)) as 四月, sum(decode(t_month,5,t_amt,0)) as 五月, sum(decode(t_month,6,t_amt,0)) as 六月, sum(decode(t_month,7,t_amt,0)) as 七月, sum(decode(t_month,8,t_amt,0)) as 八月, sum(decode(t_month,9,t_amt,0)) as 九月, sum(decode(t_month,10,t_amt,0)) as 十月, sum(decode(t_month,11,t_amt,0)) as 十一月, sum(decode(t_month,12,t_amt,0)) as 十二月, sum(t_amt) 总计 from temp t group by t_year order by t_year;

结果集

 

4、使用 pivot（行转列）

with temp as ( select 1 id, 'A' name, 1000 amt from dual union all select 2 id, 'B' name, 2000 amt from dual union all select 3 id, 'B' name, 3000 amt from dual union all select 4 id, 'A' name, 4000 amt from dual union all select 5 id, 'C' name, 5000 amt from dual union all select 6 id, 'C' name, 6000 amt from dual union all select 7 id, 'C' name, 7000 amt from dual union all select 8 id, 'D' name, 8000 amt from dual ) select * from (select name,sum(amt) amt from temp group by name) -- 数据源 pivot (sum(amt) for name in ('A','B','C','D','E')); -- E 不存在

结果集