將直式資料以橫式方式(二維方式)顯示-UNION方式

以下sql可以將直列式之資料轉換為橫式列印
不過，前提為橫列之項目須固定，否則橫列之項目就必須以程式動態組出


--抓庫存轉成二維範例
--原檔案(對照用)
SELECT C.GONO1, C.GONO2, C.GONO3, C.INPQTY, C.BAKQTY, C.DINPQTY, C.DBAKQTY, C.CALQTY, C.OTHQTY, C.TOTQTY, Z.COLS
FROM FDRCGOMI C
LEFT JOIN FDRTDSIZE Z ON Z.SPNO = C.SPNO AND Z.SIZES = C.GONO3
WHERE C.SPNO = '007'
ORDER BY GONO1

--轉成二維
SELECT SPNO, GONO1, SUM(TOTQTY) AS TOTQTY
, SUM(CASE WHEN COLS = '01' THEN TOTQTY ELSE 0 END) AS SIZE01
, SUM(CASE WHEN COLS = '02' THEN TOTQTY ELSE 0 END) AS SIZE02
, SUM(CASE WHEN COLS = '03' THEN TOTQTY ELSE 0 END) AS SIZE03
, SUM(CASE WHEN COLS = '04' THEN TOTQTY ELSE 0 END) AS SIZE04
, SUM(CASE WHEN COLS = '05' THEN TOTQTY ELSE 0 END) AS SIZE05
, SUM(CASE WHEN COLS = '06' THEN TOTQTY ELSE 0 END) AS SIZE06
, SUM(CASE WHEN COLS = '07' THEN TOTQTY ELSE 0 END) AS SIZE07
, SUM(CASE WHEN COLS = '08' THEN TOTQTY ELSE 0 END) AS SIZE08
, SUM(CASE WHEN COLS = '09' THEN TOTQTY ELSE 0 END) AS SIZE09
, SUM(CASE WHEN COLS = '10' THEN TOTQTY ELSE 0 END) AS SIZE10
, SUM(CASE WHEN COLS = '11' THEN TOTQTY ELSE 0 END) AS SIZE11
, SUM(CASE WHEN COLS = '12' THEN TOTQTY ELSE 0 END) AS SIZE12
, SUM(CASE WHEN COLS = '13' THEN TOTQTY ELSE 0 END) AS SIZE13
, SUM(CASE WHEN COLS = '14' THEN TOTQTY ELSE 0 END) AS SIZE14
FROM (
--原來的資料
SELECT C.SPNO, C.GONO1, C.GONO2, C.GONO3, C.INPQTY, C.BAKQTY, C.DINPQTY, C.DBAKQTY, C.CALQTY, C.OTHQTY, C.TOTQTY, Z.COLS
FROM FDRCGOMI C
LEFT JOIN FDRTDSIZE Z ON Z.SPNO = C.SPNO AND Z.SIZES = C.GONO3
WHERE C.SPNO = '007'
AND COLS IS NOT NULL
--原來的資料結束
) AS A
GROUP BY SPNO, GONO1
ORDER BY SPNO, GONO1