從 SQL Server 2005 起, 支援了 Pivot 語法及 UnPivot 語法.
利用 Pivot 語法, 可以做出一些轉置及特殊報表使用的查詢結果(對於要分析資料時特別有用).
先來建立範例資料, 這個資料表是一個流水號(logid, 一個會員編號 userno, 一個投票標的號 voteid, 一個時間欄位 createtime), 為能將資料表模擬真實的狀況, 建立一些資料來供應用, script 如下:
CREATE TABLE VOTELOG (logid int identity(1,1), userno int, voteid int, createtime datetime default getdate())
insert into VOTELOG (userno, voteid, createtime) values (1, 1, '2010/7/1 1:15')
insert into VOTELOG (userno, voteid, createtime) values (2, 1, '2010/7/1 3:27')
insert into VOTELOG (userno, voteid, createtime) values (4, 2, '2010/7/1 3:29')
insert into VOTELOG (userno, voteid, createtime) values (15, 2, '2010/7/1 16:31')
insert into VOTELOG (userno, voteid, createtime) values (35, 1, '2010/7/1 17:31')
insert into VOTELOG (userno, voteid, createtime) values (24, 1, '2010/7/1 17:38')
insert into VOTELOG (userno, voteid, createtime) values (8, 2, '2010/7/1 20:18')
insert into VOTELOG (userno, voteid, createtime) values (7, 2, '2010/7/2 2:22')
insert into VOTELOG (userno, voteid, createtime) values (32, 1, '2010/7/2 2:36')
insert into VOTELOG (userno, voteid, createtime) values (9, 1, '2010/7/2 10:09')
insert into VOTELOG (userno, voteid, createtime) values (25, 2, '2010/7/2 13:35')
insert into VOTELOG (userno, voteid, createtime) values (19, 2, '2010/7/2 18:19')
insert into VOTELOG (userno, voteid, createtime) values (16, 2, '2010/7/2 19:28')
insert into VOTELOG (userno, voteid, createtime) values (6, 1, '2010/7/2 23:06')
insert into VOTELOG (userno, voteid, createtime) values (36, 2, '2010/7/3 0:42')
insert into VOTELOG (userno, voteid, createtime) values (18, 2, '2010/7/3 2:19')
假如現在我們需要分時的每日投票狀況的報表, 可以使用如下的 pivot查詢:
select dates, [0],[1],[2],[3],[4],[5],[6],[7], [8],[9],[10],[11],[12],[13],[14],[15], [16],[17],[18],[19],[20],[21],[22],[23]
from (select convert(varchar, createtime, 111) as dates, datepart(hh, createtime) as hours from votelog ) as cum
pivot (count(hours) for hours in ( [0],[1],[2],[3],[4],[5],[6],[7], [8],[9],[10],[11],[12],[13],[14],[15], [16],[17],[18],[19],[20],[21],[22],[23] )) as pv
查詢結果如下:
這樣可以很清楚地呈現出, 每日分時的統計狀況, 當然, 若是要查的是分開兩個候選人的結果, 僅需要在中間的子查詢(cum)的裡面多加入 where 條件即可.
另外多提一個部分, 這樣的查詢, 可以在子查詢先做集總, 再進行 pivot, 效果會更好(尤其在資料量愈大時), 語法如下:
select dates, [0],[1],[2],[3],[4],[5],[6],[7], [8],[9],[10],[11],[12],[13],[14],[15], [16],[17],[18],[19],[20],[21],[22],[23]
from (select convert(varchar, createtime, 111) as dates, datepart(hh, createtime) as hours, count(*) as counts from votelog group by convert(varchar, createtime, 111), datepart(hh, createtime) ) as cum
pivot (count(counts) for hours in ( [0],[1],[2],[3],[4],[5],[6],[7], [8],[9],[10],[11],[12],[13],[14],[15], [16],[17],[18],[19],[20],[21],[22],[23] )) as pv
也就是在子查詢中(cum), 先 group 好, 再進行 pivot 的查詢(但記得要用sum前面count結果), 這樣的效果會更有效率.
pivot 及 unpivot 語法可以參考以下資料:
使用 PIVOT 和 UNPIVOT: http://technet.microsoft.com/zh-tw/library/ms177410%28SQL.90%29.aspx
另外有人整理好的 cross-table stored procedure也很方便, 可以參考這篇: http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables
