oracle列转行 listagg

oracle 列转行listagg实现方式

当结果中只有要转化的那一列时候

select listagg(t.shopName, ',') within group(order by t.idCno)
from(
	select idCno, shopName 
	from shop 
	where 
	idCno in (select p.idCno from shop p)
) t
group by t.idCno

注意：
后边一定要加group by， 不然实现不了

当结果中只有要转化的那两列时候

select t.idCno, listagg(t.shopName, ',') within group(order by t.idCno)
from(
	select idCno, shopName 
	from shop 
	where 
	idCno in (select p.idCno from shop p)
) t
group by t.idCno

复杂点的逻辑应用

select l.userNo, l.userName, l.companyCom, l.manageCom, 
l.oaCno, lmg.menuName, r.organize
from Users l
left join usersAndMenu ltm on l.userId = ltm.userId
left join MenuGroup lmg on ltm.MenuGroupId = lmg.MenuGroupId
left join MenuGroupAndMenu lmtm on lmg.MenuGroupId = lmtm.MenuGroupId 
left join Menu lm on lm.MenuId = lmtm.menuId 
left join (
	select t.parentId, listAgg(t.name, ',')within group(order by t.parentId) name
	from (
		select parentId, name 
		from Menu
		where parentId in(select m.MenuId from Menu m)
	) t
	-- 此处group by 是列转行必须有，不然无法实现列转行
	group by t.parentId
) r on r.parentId = lm.menuId
-- 此处group by因为查出的数据有重复，需要去重
group by l.userNo, l.userName, l.companyCom, l.manageCom, 
l.oaCno, lmg.menuName, r.organize

共同探讨学习技术创建技术氛围Day9884125