TIPS-Using PIVOT In SQL 2005

遇到一個可以應用SQL 2005 PIVOT新功能的好例子，拿出來分享一下。

分析IIS Log時，我希望統計每一秒鐘不同執行結果(Status Code=200, 304, 401, 404, 500 ...)的次數，由IISLog匯入而成的Table可以找到LogTime與StatusCode兩個欄位。
要統計每秒不同StatusCode的數量不難，例如以下的T-SQL:

As Cnt 

FROM IISLogTable

BY LogTime, StatusCode

BY LogTime

查出的結果會像這樣:

LogTime  StatusCode Cnt
-------- ---------- -----------
06:40:05 200        5
06:40:05 302        1
06:40:06 304        1
06:40:06 200        10
06:40:07 200        5
06:40:07 500        3
06:40:08 200        11
06:40:08 404        1

但在實際應用時，我們會希望將每秒鐘的各StatusCode整合在一列中，像LogTime, StatusCode, Count200, Count302, Count304, Count401...的形式，以方便用Excel製表或繪圖，但這少不了要費一番手腳。

當使用的資料庫是SQL 2005時，我們多了一項新武器---PIVOT! 用如下的語法，就可以將以上的結果由列轉成欄，變成我們要的樣子。

FROM 

(

AS Cnt

    FROM IISLogTable

BY LogTime, StatusCode

) AS X

PIVOT

(

SUM(Cnt)

    IN 

    ([200],[302],[304],[401],[404],[500])

) AS PVT

查詢結果變成:

LogTime  200         302         304         401         404         500
-------- ----------- ----------- ----------- ----------- ----------- -----------
06:40:05 5           1           NULL        NULL        NULL        NULL
06:40:06 10          NULL        1           NULL        NULL        NULL
06:40:07 5           NULL        NULL        NULL        NULL        3
06:40:08 11          NULL        NULL        NULL        1           NULL

怎樣，很省事很酷吧?