行转列、列转行题目(收集中...)

一、列转行

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;