Oracle行列转换

行列转换，查询汇总。

 

select item_desc,spec ,
max(case invoice_date when CONCAT(to_char(sysdate,'yyyy'),'01') then amt else 0 end) 一月金额,
max(case invoice_date when CONCAT(to_char(sysdate,'yyyy'),'01') then settle_qty else 0 end) 一月数量,
max(case invoice_date when CONCAT(to_char(sysdate,'yyyy'),'02') then amt else 0 end) 二月金额,
max(case invoice_date when CONCAT(to_char(sysdate,'yyyy'),'02')then settle_qty else 0 end) 二月数量,
max(case invoice_date when CONCAT(to_char(sysdate,'yyyy'),'03') then amt else 0 end) 三月金额,
max(case invoice_date when CONCAT(to_char(sysdate,'yyyy'),'03') then settle_qty else 0 end) 三月数量
from (select substr(t2.invoice_date, 1, 6) as invoice_date,
       sum(t.settle_qty) as settle_qty,
       sum(t.amt) as amt,
       t.item_desc,
       t.spec
  from BSSIM.SS_SETTLE_D t
  left join BSSIM.SS_INVOICE_M t2
  on t.INVOICE_SYSID = t2.INVOICE_SYSID
 group by t.item_id, t.item_desc, t.spec, substr(t2.invoice_date, 1, 6)
 order by substr(t2.invoice_date, 1, 6))
group by item_desc, spec

参考：Oracle行列转换