行转列、列转行题目(收集中...)-优快云博客

本文详细介绍了SQL中的列转行与多行转一行两种常见操作，通过实例展示了如何使用MAX+DECODE+GROUPBY方法进行列转行，以及如何使用LISTAGG函数结合WITHIN GROUP子句实现多行转一行。适用于数据库管理和SQL查询学习。

一、列转行

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

二、多行转一行

select listagg(ename,',')
within group(order by empno desc)
from emp;