数据库多行拼接到一行Oracle和sqlServer

1、Oracle使用

原来效果 要求效果 大字段返回

如上图所示：role_cd字段代表权限，portal_cd代表门户菜单，一个role_cd可能对应着多个portal_cd，要想将portal_cd合并到一行。

--原始sql
select role_cd,portal_cd from xap_role_portal  
where role_cd='XAPM14.01' and del_f ='0'

--修改后
select role_cd,
       listagg(portal_cd, ',') within group(order by role_cd) as "portal_nm"
  from xap_role_portal
 where role_cd = 'XAPM14.01'
   and del_f = '0'
 group by role_cd

--拼接后数据长度超过4000
select role_cd,
       xmlagg(xmlparse(content portal_cd || ',') order by role_cd).getclobval() as "portal_nm"
  from xap_role_portal
 where role_cd = 'XAPM14.01'
   and del_f = '0'
 group by role_cd

xmlagg、xmlparse返回的是clob类型的数据，在后台用map接收即可。

sqlServer：

原来效果 目标效果

 select xr.role_cd,
        portal_nm = stuff((
        SELECT ',' + xp.nm
        FROM xap_role_portal xrp
        left join xap_portal xp
        on xrp.portal_cd = xp.portal_cd
        WHERE 1=1
        and xrp.role_cd = 'XAPM14.01'
        and ISNULL(xrp.del_f,'0') ='0'
        and ISNULL(xp.del_f,'0') ='0'
        FOR XML path('')
        ), 1, 1, '')
        from xap_role xr
        where 1=1
        and ISNULL(xr.del_f,'0') ='0'
        and ISNULL(xr.del_f,'0') ='0'
        and xr.role_cd = 'XAPM14.01'
        group by role_cd
        order by role_cd

原来的效果

如上，想要按照group_id进行分组，然后将user_id关联另一个表中的字段，查询出姓名，然后插入到一条数据中。

select ngm.group_id,
       ngm.group_name,
       user_nm = stuff((SELECT ',' + oe.nm
                         FROM nur_group_manage ngm2
                         left join org_emp oe
                           on oe.emp_no = ngm2.user_id
                        WHERE 1 = 1
                          and group_id = ngm.group_id
                          and ISNULL(oe.del_f, '0') = '0'
                          FOR XML path('')),
                       1,
                       1,
                       '')
  from nur_group_manage ngm
 where 1 = 1
   and ISNULL(ngm.del_f, '0') = '0'
 group by group_id, ngm.group_name

目的效果

如上，圈出来的那个条件必须要加，需要跟外边的表做关联，不然会发现多行数据都是一样的，并没有进行group by。