SQL Pivot 範例

在SQL 2008以後的MSSQL中，PIVOT函數可將指定資料庫欄位的值產生分類小計後橫向展開，達到交叉表的效果。

舉例而言有個資料表叫做PLAN，裡面有兩個欄位 SCHOOL_NO,ACTION_CODE記載學校代碼與學校申請的項目。
若我們要知道每間學校每個申請項目的筆數，直覺會用 group by去小計，
但這樣查出來的結果還是一列一列的，並不會橫向展開。

select SCHOOL_NO,ACTION_CODE, count(*) group by SCHOOL_NO,ACTION_CODE 
 

清大 1 3 
清大 2 2 
清大 3 11 
政大 3 3 

可是一般正常人習慣看的，是將結果列出來的情形，比較清楚易懂。

     1 2 3 
清大 3 2 11 
政大 0 0 3 
 

筆者是從SQL2000開始學習的，以前必須先將結果 用case when ACTION_CODE = '2' then count else 0 的方式，
將每一個可能的結果擺到橫軸顯示。
但在SQL 2005以後提供了pivot函數，就不需要這樣做(仍須看貴司DBA設定DB的相容等級>=90才能使用)。
以下面的範例，查詢區塊t先過濾出要彙整計算的資料集，
第一個欄位通常是學校或部門代碼
區塊p 則是告訴SQL 要用哪一個欄位計算小計
先輸入是要計算筆數或是加總
for 語法則讓您輸入要列出統計的欄位值，如果想看ACTION_CODE的值是1,2,3的
要寫成 [1],[2],[3]而不是一般 SQL 用兩個單引號包起來的樣式。
如果要顯示的是每月的薪資小計，就會是[1],[2]....一直列到12去了。

 SELECT c.SCHOOL_NAME, p.* 
 FROM (
  select  SCHOOL_NO,ACTION_CODE
   from PLAN  
   where PLAN_ID like'%1A%'
   --and STATUS in ('0','1','2')
 ) as t 
 PIVOT (
  count(ACTION_CODE) 
  FOR ACTION_CODE IN ([2],[21],[22])
 ) as p
 left join SCHOOL as c
 on p.SCHOOL_no = c.SCHOOL_NO
 where p.[2] >0 

---

如果選項非常多，又要手工輸入所有項目，感覺有點瞎?
這時候可以利用SQL內宣告變數搭配select語法，兜出字串再塞到 Pivot裡。

DECLARE @ParaList NVARCHAR(MAX), @PivotSQL NVARCHAR(MAX) 
 
SELECT @ParaList = COALESCE(@ParaList + ',' ,'' ) + QUOTENAME(SCHOOL_NO) 
FROM SCHOOL 
GROUP BY QUOTENAME(SCHOOL_NO) 

print @ParaList

完成版

/*宣告變數*/
 DECLARE @ParaList NVARCHAR(MAX), @PivotSQL NVARCHAR(MAX) 

/*用現有資料表欄位值組出分類列表*/
/*這裡不能用distinct，不然只會出現第一筆 */
SELECT  @ParaList = COALESCE(@ParaList + ',' ,'' ) + QUOTENAME(rtrim(ACTION_CODE)) 
FROM PROFESSION_PLAN  
group by ACTION_CODE
print  @ParaList

 /*將查詢語法改為一段TSQL，帶入參數後用sp_executesql執行*/

 SELECT @PivotSQL = N'
 SELECT c.SCHOOL_NAME, p.* 
 FROM (
  select  SCHOOL_NO,ACTION_CODE
   from PROFESSION_PLAN  
   where  PROFESSION_PLAN_ID like''%1A%''
   --and STATUS in (''0'',''1'',''2'')
 ) as t 
 PIVOT (
  count(ACTION_CODE) 
  FOR ACTION_CODE IN (' + @ParaList +  N')
 ) as p
 left join SCHOOL as c
 on p.SCHOOL_no = c.SCHOOL_NO
 where p.[2] >0 
 '
 
EXEC sp_executesql  @PivotSQL;