多行转一行:
表结构:
1 A
1 B
1 C
2 A
2 B
3 C
3 F
4 D
转换后变成:
1 A,B,C
2 A,B
3 C,F
4 D
这个不排序:
SELECT a.id,wm_concat (a.remark) new_result
FROM
tb_name a
group by
a.id
这个是分组排序的:
select t.xh,listagg(LCMC,'|') within group (order by LCMC) from JH_LCSJ t group by t.xh;
转换后变成:
1 A|B|C
2 A|B
3 C|F
4 D
列转行函数:LISTAGG()
先看示例代码:
1.withtempas(
2.select'China'nation,'Guangzhou'cityfromdualunionall
3.'Shanghai'city4.'Beijing'city5.'USA'nation,255)">'NewYork'city6.'Bostom'city7.'Japan'nation,255)">'Tokyo'cityfromdual
8.)
9.selectnation,listagg(city,255)">',')withinGROUP(orderbycity)
10.fromtemp
11.groupbynation
这是最基础的用法:
LISTAGG(XXX,XXX) WITHIN GROUP( ORDER BY XXX)
用法就像聚合函数一样,通过Group by语句,把每个Group的一个字段,拼接起来。
非常方便。
就是over(partition by XXX)
也就是说,在你不实用Group by语句时候,也可以使用LISTAGG函数:
select500population,85)">select1500population,85)">select1000population,85)">selectpopulation,
10.nation,250)">11.city,250)">12.listagg(city,85)">bycity)over(partitionbynation)rank
13.temp
总结一下:LISTAGG()把它当作SUM()函数来使用就可以。
原文链接:https://www.f2er.com/oracle/210852.html