oracle中行转列LISTAGG()函数详解_listagg函数用法

1.LISTAGG()函数作为普通函数使用时就是查询出来的结果列转为行

SELECT
	LISTAGG ( NAME_CHS, ',' ) within GROUP ( ORDER BY ROWNUM ) name 
FROM
	GSPUSER 
WHERE
	ROWNUM <= 10

2.LISTAGG()作为分组函数使用

例如，把每个班组下面的人员拼接成一行

SELECT
	b.MOMTEAMNAME,
	LISTAGG ( g.NAME_CHS, ',' ) WITHIN GROUP ( ORDER BY b.MOMTEAMNAME ) res
FROM
	DGMOMPTDGMOMGLHQYBZGL b 
	LEFT JOIN DGMOMPTABOUTUSERS u ON b.ID = u.PARENTID 
	LEFT JOIN GSPUSER g ON u.MOMEMPLOYEEID = g.ID 
WHERE
	b.MOMDATASTATE = 0 
	AND u.MOMDATASTATE = 0 
GROUP BY
	b.MOMTEAMNAME

3.LISTAGG()做分析函数使用

例如，查询每个班组下面有哪些人，统计每组人数量

SELECT
    MOMTEAMNAME,RES,COUNT
FROM(	
 SELECT
	b.MOMTEAMNAME,
	LISTAGG ( g.NAME_CHS, ',' ) WITHIN GROUP ( ORDER BY b.MOMTEAMNAME ) over(partition by b.MOMTEAMNAME) res,
	count(g.NAME_CHS) over(partition by b.MOMTEAMNAME) count,
	row_number() over(partition by b.MOMTEAMNAME ORDER BY ROWNUM) rn
FROM
	DGMOMPTDGMOMGLHQYBZGL b 
	LEFT JOIN DGMOMPTABOUTUSERS u ON b.ID = u.PARENTID 
	LEFT JOIN GSPUSER g ON u.MOMEMPLOYEEID = g.ID 
WHERE
	b.MOMDATASTATE = 0 
	AND u.MOMDATASTATE = 0 
) WHERE rn = 1

以上是个人开发中遇到的情况，有不当之处还请指正，欢迎评论区讨论