4. Dynamic SQL

使用dynamic SQL可以在以下情況里對所有的表創建一個通用查詢：在primary key里沒有包含到列，但是有可兼容的數據類型存在時。

DECLARE@table_name SYSNAME
SELECT@table_name ='dbo.Players'
 
DECLARE@SQL NVARCHAR(MAX)
SELECT@SQL ='
SELECT *
FROM '+ @table_name +'
UNPIVOT (
    value FOR code IN (
        '+ STUFF((
    SELECT', ['+ c.name+']'
    FROMsys.columns cWITH(NOLOCK)
    LEFTJOIN(
        SELECTi.[object_id], i.column_id
        FROMsys.index_columns iWITH(NOLOCK)
        WHEREi.index_id = 1
    ) iONc.[object_id] = i.[object_id]ANDc.column_id = i.column_id
    WHEREc.[object_id] = OBJECT_ID(@table_name)
        ANDi.[object_id]ISNULL
    FORXML PATH(''), TYPE).value('.','NVARCHAR(MAX)'), 1, 2,'') +'
    )
) unpiv'
 
PRINT @SQL
EXECsys.sp_executesql @SQL

其結果為：

SELECT*
FROM<table_name>
UNPIVOT (
    valueFORcodeIN(<unpivot_column>)
) unpiv

這個方法在速度上會比較慢一些，是因為UNPIVOT的自動生成查詢需要從系統里進行額外的讀取以及通過XML trick進行“行”的串聯。

5. XML

一個更聰明的辦法來執行 dynamic UNPIVOT，是通過對XML做一個小竅門：

SELECT
      p.PlayerID
    , GameCount = t.c.value('.','INT')
    , GameType = t.c.value('local-name(.)','VARCHAR(10)')
FROM(
    SELECT
          PlayerID
        , [XML] = (
                SELECTWin, Defeat, StandOff
                FORXML RAW('f'), TYPE
            )
    FROMdbo.Players
) p
CROSSAPPLY p.[XML].nodes('f/@*') t(c)

按照以上的查詢， SQL Server會對每一個行都生成一個XML：

<f Column1="Value1" Column2="Value2" Column3="Value3" ... />

接下來就是屬性的名稱和值被解析。大多數情況下，XML的使用會導致一個更慢的執行計劃：

我們來對比一下通過執行 Compare Selected Results指令的結果：

我們可以看到，在執行查詢的速度上，UNPIVOT和VALUES沒有太明顯的區別。當然，這只是針對簡單的將列轉化為行的任務而言。

現在我們用UNPIVOT語句再來做另外一個實驗：

SELECT
      PlayerID
    , GameType = (
        SELECTTOP1 GameType
        FROMdbo.Players
        UNPIVOT (
            GameCountFORGameTypeIN(
                Win, Defeat, StandOff
            )
        ) unpvt
        WHEREPlayerID = p.PlayerID
        ORDERBYGameCountDESC
    )
FROMdbo.Players p

這次的執行計劃的瓶頸是對多重數據的讀取和排序：

要解決這個瓶頸其實是相當的簡單，我們可以使用來自外部查詢的列的模塊就能避免多重數據讀取的問題：

SELECT
      p.PlayerID
    , GameType = (
        SELECTTOP1 GameType
        FROM(SELECTt = 1) t
        UNPIVOT (
            GameCountFORGameTypeIN(
                Win, Defeat, StandOff
            )
        ) unpvt
        ORDERBYGameCountDESC
    )
FROMdbo.Players p

這樣一來讀取多重的數據就被避免了，但是另外一個最消耗資源的操作——排序，仍然存在：

接下來就需要VALUES語句來發揮其作用了：

SELECT
      t.PlayerID
    , GameType = (
            SELECTTOP1 GameType
            FROM(
                VALUES
                      (Win, 'Win')
                    , (Defeat,  'Defeat')
                    , (StandOff,'StandOff')
            ) t (GameCount, GameType)
            ORDERBYGameCountDESC
        )
FROMdbo.Players t

現在就如我們所預期的那樣，執行計劃被簡化了，但是排序依然存在：

讓我們嘗試使用aggregation功能來消除掉排序：

SELECT
      t.PlayerID
    , GameType = (
            SELECTTOP1 GameType
            FROM(
                VALUES
                      (Win, 'Win')
                    , (Defeat,  'Defeat')
                    , (StandOff,'StandOff')
            ) t (GameCount, GameType)
            WHEREGameCount = (
                SELECTMAX(Value)
                FROM(
                    VALUES(Win), (Defeat), (StandOff)
                ) t(Value)
            )
        )
FROMdbo.Players t

現在，執行計劃就如下圖所示了：

結論：當我們需要在SQL SERVER里執行一個簡單的將列轉化為行的任務時，比較好的選擇是使用 UNPIVOT或者VALUES結構。如果轉換后的數據行是用作聚合或排序時，則最好使用VALUES結構，因為它能生成一個更有效率的執行計劃。

>>點此免費下載試用dbForge Studio for SQL Server