從 SQL Server 2005 起, 支援了 Pivot 語法及 UnPivot 語法.
利用 Pivot 語法, 可以做出一些轉置及特殊報表使用的查詢結果(對於要分析資料時特別有用).
先來建立範例資料, 這個資料表是一個流水號(logid, 一個會員編號 userno, 一個投票標的號 voteid, 一個時間欄位 createtime), 為能將資料表模擬真實的狀況, 建立一些資料來供應用, script 如下:
01.
CREATE
TABLE
VOTELOG (logid
int
identity(1,1), userno
int
, voteid
int
, createtime datetime
default
getdate())
02.
insert
into
VOTELOG (userno, voteid, createtime)
values
(1, 1,
'2010/7/1 1:15'
)
03.
insert
into
VOTELOG (userno, voteid, createtime)
values
(2, 1,
'2010/7/1 3:27'
)
04.
insert
into
VOTELOG (userno, voteid, createtime)
values
(4, 2,
'2010/7/1 3:29'
)
05.
insert
into
VOTELOG (userno, voteid, createtime)
values
(15, 2,
'2010/7/1 16:31'
)
06.
insert
into
VOTELOG (userno, voteid, createtime)
values
(35, 1,
'2010/7/1 17:31'
)
07.
insert
into
VOTELOG (userno, voteid, createtime)
values
(24, 1,
'2010/7/1 17:38'
)
08.
insert
into
VOTELOG (userno, voteid, createtime)
values
(8, 2,
'2010/7/1 20:18'
)
09.
insert
into
VOTELOG (userno, voteid, createtime)
values
(7, 2,
'2010/7/2 2:22'
)
10.
insert
into
VOTELOG (userno, voteid, createtime)
values
(32, 1,
'2010/7/2 2:36'
)
11.
insert
into
VOTELOG (userno, voteid, createtime)
values
(9, 1,
'2010/7/2 10:09'
)
12.
insert
into
VOTELOG (userno, voteid, createtime)
values
(25, 2,
'2010/7/2 13:35'
)
13.
insert
into
VOTELOG (userno, voteid, createtime)
values
(19, 2,
'2010/7/2 18:19'
)
14.
insert
into
VOTELOG (userno, voteid, createtime)
values
(16, 2,
'2010/7/2 19:28'
)
15.
insert
into
VOTELOG (userno, voteid, createtime)
values
(6, 1,
'2010/7/2 23:06'
)
16.
insert
into
VOTELOG (userno, voteid, createtime)
values
(36, 2,
'2010/7/3 0:42'
)
17.
insert
into
VOTELOG (userno, voteid, createtime)
values
(18, 2,
'2010/7/3 2:19'
)
假如現在我們需要分時的每日投票狀況的報表, 可以使用如下的 pivot查詢:
1.
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]
2.
from
(
select
convert
(
varchar
, createtime, 111)
as
dates, datepart(hh, createtime)
as
hours
from
votelog )
as
cum
3.
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, 效果會更好(尤其在資料量愈大時), 語法如下:
1.
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]
2.
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
3.
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