在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;