oracle 多行转一列

以前用过又忘了。。今天做数据导出用到，记下。

Oracle中的wmsys.wm_concat主要实现行转列功能(说白了就是将查询的某一列值使用逗号进行隔开拼接，成为一条数据)。 

select d.id,
       d.papid as 援助系统,
       i.papname,
       d.fid as 发货单号,
       d.fwdate as 发货时间,
       w.id,
       w.wid as 发货传票号,
       w.pwunit as 慈善会,
       m.manyccfname,
       w.standard as 规格,
       s.standardname,
       w.pwcname as 收货人,
       w.fwcount as 发货数量,
       w.ischeck as 是否确认,
       w.pwcount as 实收数量多个规格,
       w.losecount as 损失数量,
       w.pwdate as 收货时间,
       to_char(wmsys.wm_concat(n.drugnum)) as 规格id,
       to_char(wmsys.wm_concat(r.drugid)),
       to_char(wmsys.wm_concat(r.shelflife)),
       to_char(wmsys.wm_concat(n.drugcount)) as 规格对应药品数量
  from tb_dispatchlist d
  left join tb_waybill w
    on d.id = w.dlid
  left join tb_waybillbydrugnum n
    on w.id = n.wid
  left join tb_druginfo r
    on r.id = n.drugnum
  left join tb_manyccfinfo m
    on w.pwunit = m.id
  left join tb_papinfo i
    on i.id = d.papid
  left join tb_pap_standard s
    on s.id = w.standard
 where d.dstatus = '0'
   and w.statu = '0'
 group by d.id,
          d.papid,
          i.papname,
          d.fid,
          d.fwdate,
          w.id,
          w.wid,
          w.pwunit,
          m.manyccfname,
          w.standard,
          s.standardname,
          w.pwcname,
          w.fwcount,
          w.ischeck,
          w.pwcount,
          w.losecount,
          w.pwdate
 order by d.id desc, d.fwdate desc, w.wid asc;

 

咳咳。。。sorry ，上面SQL存在问题。

如果只是单纯的把一个字段多行转一列，上述是没有问题的，只需使用wm_concat函数即可。但如上面代码中所示，如果有多个字段都需多行转一列，该方法并不能解决数据对应问题，故仍需改进。

 

select t.did,
       t.papid,
       t.papname,
       t.fid,
       t.fwdate,
       t.id,
       t.wid,
       t.pwunit,
       t.manyccfname,
       t.standard,
       t.pwcname,
       t.fwcount,
       t.ischeck,
       t.pwcount,
       t.losecount,
       t.pwdate,
       t.remark,
       max(t.drugidstr),
       max(t.shelflifestr),
       max(t.drugcountstr)
  from (select d.id did,
               w.papid,
               i.papname,
               d.fid,
               d.fwdate,
               w.id,
               w.wid,
               w.pwunit,
               m.manyccfname,
               w.standard,
               s.standardname,
               w.pwcname,
               w.fwcount,
               w.ischeck,
               w.pwcount,
               w.losecount,
               w.pwdate,
               w.remark,
               wmsys.wm_concat(r.drugid) over(partition by w.id order by r.id) drugidstr,
               wmsys.wm_concat(r.shelflife) over(order by r.id) shelflifestr,
               wmsys.wm_concat(n.drugcount) over(order by r.id) drugcountstr
          from tb_dispatchlist d
          left join tb_waybill w
            on d.id = w.dlid
          left join tb_waybillbydrugnum n
            on w.id = n.wid
          left join tb_druginfo r
            on r.id = n.drugnum
          left join tb_manyccfinfo m
            on w.pwunit = m.id
          left join tb_papinfo i
            on i.id = w.papid
          left join tb_pap_standard s
            on s.id = w.standard
         where d.dstatus = '0'
           and w.statu = '0'
           and n.status = '0'        
        ) t
 group by t.did,
          t.papid,
          t.papname,
          t.fid,
          t.fwdate,
          t.id,
          t.wid,
          t.pwunit,
          t.manyccfname,
          t.standard,
          t.standardname,
          t.pwcname,
          t.fwcount,
          t.ischeck,
          t.pwcount,
          t.losecount,
          t.pwdate,
          t.remark
 order by t.did desc, t.fwdate desc, t.wid asc

 若不加 partition by 会报 "ORA-01467 sort key too long" 错误

 若有更加优化的sql，请指正。