oracle将多条数据合并成一条

select tbr_id ,wm_concat(t.zz_name) as zzName from Gp_Gcjs_Tbr_Zz t  group by tbr_id

 

下面做的一个例子，东西有点多。

 

select rownum as xh, ts.*
  from (select t1.id,
               t2.id as licenseId,
               t3.org_pers_id as persId,
               t1.local_code as loaclCode,
               t1.local_name as localName,
               t1.agc_cn_name as agcCnName,
               t1.bi_reg_addr as biRegAddr,
               t1.local_name as yydz,
               t3.pers_name as faren,
               t3.link_phone as frPhone,
               t3.mobile as frTelePhone,
               t1.contact_people as contactPeople,
               t1.contact_phone as contactPhone,
               t1.phone_num as contactTelePhone,
               t2.rank as rank,
               decode(t2.rank, 1, '甲级', '0', '乙级', '') as rankName,
               t2.license_time_star as rantStart,
               to_char(t2.license_time_star, 'yyyy-MM-dd') as rankStartStr,
               t2.license_time_end as rankEnd,
               to_char(t2.license_time_end, 'yyyy-MM-dd') as rankEndStr,
               t1.reg_method as regMethod,
               decode(t1.reg_method, 1, '注册申请', '0', '备案申请', '') as regMethodName,
               t2.valid_record_star as validStart,
               t2.valid_record_end as validEnd,
               to_char(t2.valid_record_star, 'yyyy-MM-dd') as validStartStr,
               to_char(t2.valid_record_end, 'yyyy-MM-dd') as validEndStr,
               tt.valid as valid,
               tt.validName as validName,
               '' as khqk,
               '' as fgsName,
               '' as gfsFzr,
               tn.shsj1 as shsj1,
               tn.shsj2 as shsj2,
               t1.notes as notes
          from (select id,
                       agency_id as agencyId,
                       decode(sign(sign(valid_record_end - sysdate) +
                                   sign(sysdate - valid_record_star)),
                              '1',
                              '1',
                              '0') as valid,
                       decode(sign(sign(valid_record_end - sysdate) +
                                   sign(sysdate - valid_record_star)),
                              '1',
                              '有效',
                              '无效') as validName
                  from gp_agency_license) tt,
               gp_agency t1
          left join gp_agency_license t2 on t1.id = t2.agency_id
          left join sys_org_pers t3 on t1.agc_orgcode = t3.jgdm
                                   and t3.pers_type = '3'
          left join (select sup_id,
                           substr(dateStr, 0, instr(dateStr, ',', 1, 1) - 1) as shsj1,
                           substr(dateStr,
                                  instr(dateStr, ',', 1, 1) + 1,
                                  instr(dateStr, ',', 1, 2) -
                                  instr(dateStr, ',', 1, 1) - 1) as shsj2
                      from (select sup_id,
                                   wm_concat(to_char(md_time, 'yyyy-MM-dd')) as dateStr
                              from (select sup_id, md_time
                                      from gp_xy_modify t
                                     where t.flag = '2'
                                     order by t.md_time desc)
                             group by sup_id)) tn on tn.sup_id = t1.id
         where 1 = 1
           and t1.flag = '4'
           and t1.id = tt.agencyId) ts