一、列转行
1、把如下表
year month amount
1991 1 1.1
1991 2 1.2
1991 3 1.3
1991 4 1.4
1992 1 2.1
1992 2 2.2
1992 3 2.3
1992 4 2.4
转换为结果表
year m1 m2 m3 m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4
方法(MAX+DECODE+GROUP BY):
with t as
(
select 1991 as year, 1 as month,1.1 as amount from dual
union all
select 1991 as year, 2 as month,1.2 as amount from dual
union all
select 1991 as year, 3 as month,1.3 as amount from dual
union all
select 1991 as year, 4 as month,1.4 as amount from dual
union all
select 1992 as year, 1 as month,2.1 as amount from dual
union all
select 1992 as year, 2 as month,2.2 as amount from dual
union all
select 1992 as year, 3 as month,2.3 as amount from dual
union all
select 1992 as year, 4 as month,2.4 as amount from dual
)
select year, MAX(decode(month,1,amount,null)) m1,
MAX(decode(month,2,amount,null)) m2,
MAX(decode(month,3,amount,null)) m3,
MAX(decode(month,4,amount,null)) m4
from t
)
group by year
2. 使用11g的新关键字 pivot
with t as
(
select 'aa' a,'b11' b, 1 n from dual
union all
select 'aa' a,'b22' b, 2 n from dual
union all
select 'aa' a,'b33' b, 3 n from dual
)
select * from t pivot(max(b) for n in(1 as b1,2 as b2,3 as b3 ));
结果:
A B1 B2 B3
-- --- --- ---
aa b11 b22 b33
二、多行转一行
select listagg(ename,',')
within group(order by empno desc)
from emp;