如何使用 PIVOT「扭轉」查詢的結果?

PIVOT 翻轉Table的方法

在Oracle 和 SQLServer 都有現成的PIvot 樞紐分析函式可以用~

公式如下

PIVOT ( <aggregation function>(<column being aggregated>)

FOR [<column that contains the values that will become column headers>]

IN ( [first pivoted column], [second pivoted column], ... [last pivoted column]) ) AS <alias for the pivot table> <optional ORDER BY clause>;

PIVOT 內放運算集合
For 一個你要翻轉的欄位
IN 你對應到select 的欄位
As 為這新的PIVOT Table命名

TABLE

假設我們DB有一張表叫ONETABLE,
KIND 種類有 101,102,103 三種
table 內容如下

TYPE1	TYPE2	KIND	CREATEDATE	DELMK
A	B	101	20220401
A		102	20220505	D
A		101	20220601
A		102	20220601
B		102	20220602
B	A	102	20220608

需求

📊需求是要得到一張過濾掉條件後的表格
統計每種TYPE的每種KIND次數加總
期望要長這樣

	101	102	103
A	1	2	0
B	0	2	0

原先 SQL

那就開始吧~~~

JAVA 連接資料庫連接這段跳過
只需知道我們傳入三個欄位進DB查詢
1.TYPE類型 (LIKE A,B,C)
2.日期的起月
3.日期的迄月

-- 傳入值 type = A , 起月 = 202205 , 迄月 = 202206
-- 條件
     SELECT DECODE('A', Type1, Type1, Type2, Type2) AS Yourtype, Kind , COUNT(*) COUNT
     FROM Onetable
     WHERE TO_NUMBER(SUBSTR(Createdate,1,6)) BETWEEN TO_NUMBER('202205') AND TO_NUMBER('202206')
     AND (Delmk <> 'D' OR Delmk IS NULL)
     AND (Type1 = 'A' OR Type2 = 'A')
     GROUP BY Type1, Type2, Kind

查出來的資料應該會長這樣

YOURTYPE	KIND	COUNT
A	102	1
A	102	1
A	101	1

註(根據大表來看):
第一筆資料:CREATEDATE 時段範圍不對 X
第二筆資料:DELMK 為D註記 (表示已刪除) 已排除X
第三筆資料:TYPE1符合A , 有一筆KIND 101
第四筆資料:TYPE1符合A , 有一筆KIND 102
第五筆資料:TYPE1不符合
第六筆資料:雖TYPE1不符,但是TYPE2 符合A , 有一筆KIND 102

困擾

這樣查出來會是一個根據TYPE=A 的多筆LIST
再由JAVA 跑FOR迴圈一一塞入對應值這也是我原先的做法~ (很笨但不用多想XD)

假設找出來 A的LIST 有10筆迴圈就要跑十次
然後 TYPE有5種合計就是5*10=50次

期望

我期望查出來是一個根據TYPE=A 的單一筆統計好各KIND次數的資料
希望查出來的表是長這樣

KIND1	KIND2	KIND3
1	2	0

這樣JAVA 不用跑回圈永遠只有單一筆統計好各KIND次數的資料

假設找出來 TYPE有5種 JAVA不用迴圈只需要 5次

解決方法- 使用 PIVOT 「扭轉」查詢的結果 (把SQL查詢成果（直式），轉成（橫式）展示格式)

注意:
1.如果只用kind的101,102,103當成欄位名稱，JAVA無法對應，因為JAVA的欄位命名，不能以數字為開頭，需要另訂名字
2.pivot 不能用nvl，只能加在找完後的上方欄位

SELECT * FROM  (
  --防止 像是kind3,沒有資料會出現null,給NVL 預設 0
  SELECT nvl(kind1,0) AS kind1, nvl(kind2,0) AS kind2, nvl(kind3,0) AS kind3 FROM (
     -- 原本的SQL 條件
     SELECT DECODE('A', Type1, Type1, Type2, Type2) AS Yourtype, Kind , COUNT(*) COUNT
     FROM Onetable
     WHERE TO_NUMBER(SUBSTR(Createdate,1,6)) BETWEEN TO_NUMBER('202205') AND TO_NUMBER('202206')
     AND (Delmk <> 'D' OR Delmk IS NULL)
     AND (Type1 = 'A' OR Type2 = 'A')
     GROUP BY Type1, Type2, Kind
  ) 
  -- 用pivot 將查出來的資料 再次分析 統計 
  pivot (
    --註 看下方:為何用 SUM(COUNT) ?
    --pivot 規定不可以用 nvl(SUM(COUNT),0)
    SUM(COUNT) 
    --用Kind 當成查出來的欄位 因JAVA命名 所以AS別得名字
    FOR Kind IN (101 AS kind1, 102 AS kind2, 103 AS kind3) 
  ) 
)

BINGO~~~
查出來 type=A的就會是

KIND1	KIND2	KIND3
1	2	0

回到需求

上述SQL 可以得到第一列的 A
再來JAVA 只要在CALL一次可以得到 B的這列
因為需要分開統計所以目前僅想到這作法沒辦法一次全部條件不同不過也省下很多工了XD~

	101	102	103
A	1	2	0
B	0	2	0

註:為何用 sum(count),而不是用count(count) ?

上面說到原本SQL查出來長這樣

YOURTYPE	KIND	COUNT
A	102	1
A	102	1
A	101	1

首先我們用KIND 當成查出來的欄位
所以以KIND角度來看~

使用sum(count) ?
原因是把這個KIND的COUNT 加起來
也就是
101 -> 1
102 -> 1+1=2

那為何不用 count(count) ?
以KIND角度來看 count 是指
針對 KIND=101 這只有一筆資料所以count =1
KIND=102 這也只有一筆資料所以count =1

結語

技術債還是要還XD
剛好維護案看到一年半前自已寫的CODE
那時候也沒有研究特別得寫法
SA開的SQL就直接給他下去手寫得很酸
現在終於有重構~ 靠著GOOGLE大神學了新寫法!
希望對大家有幫助~~~

這段SQL 有寫在線上測試DB 歡迎實測~~~
http://sqlfiddle.com/#!4/a2ce3f/10

參考stackoverflow這篇
Using pivot on multiple columns of an Oracle row :
https://stackoverflow.com/questions/23939244/using-pivot-on-multiple-columns-of-an-oracle-row