|
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
|