dbForge Studio for SQL Server為有效的探索、分析SQL Server數據庫中的大型數據集提供全面的解決方案，并設計各種報表以幫助作出合理的決策。

點擊下載dbForge Studio for SQL Server最新試用版

UNPIVOT是將列轉換為行的最佳方法嗎？

首先，讓我們給出定義并突出顯示SQL Server中PIVOT和UNPIVOT運算符之間的區別。

PIVOT和UNPIVOT關系運算符用于將表值表達式更改為另一個表并涉及數據輪換。讓我們從第一個運算符開始。

當需要將表行轉換為列時，我們使用PIVOT。它還使我們能夠根據需要對最終輸出中期望的列值執行聚合。讓我們以該表為例：

如果通過第一列“屏幕”旋轉它，我們將得到以下結果：

要在SQL Server中獲得此結果，您需要運行以下腳本：

SELECT [avg_], [11], [12], [13], [14], [15] 
FROM (
  SELECT 'average price' AS 'avg_', screen, price FROM laptops) x
  PIVOT (AVG(price) FOR screen IN([11], [12], [13], [14], [15])
) pvt；

為了反轉PIVOT運算符，也就是將數據從列級轉換回行級并獲取原始表，可以使用UNPIVOT運算符。但是，請注意，UNPIVOT與PIVOT功能并非完全相反。僅在數據透視表不包含聚合數據的情況下才有可能。

PIVOT會聚合數據，并且可以將一堆行合并為一行。由于行已合并，因此UNPIVOT不會重現初始表值表達式結果。除此之外，UNPIVOT輸入中的空值在輸出中消失。當這些值消失時，表明在執行PIVOT操作之前，輸入中可能存在原始的空值。

說到PIVOT運算符，dbForge Studio for SQL Server提供了一個有用的功能，稱為透視表。明確地說，這是一個數據分析工具，可將大量數據轉換為簡明扼要的摘要。它使我們能夠輕松地重新排列和旋轉數據，從而獲得最佳布局，以更好地理解數據關系和依賴性。此功能的最大優點是它簡化了聚合過程和統計信息計數。同樣，可以打印出獲得的報告，將其導出為各種文檔格式，然后以所需格式通過電子郵件發送。

現在，讓我們更多地討論T-SQL UNPIVOT轉換的不同實現。

假設我們有一個匯總表，其中包含有關每個玩家玩游戲的結果的數據。我們有一項任務將列轉換為行。

IF OBJECT_ID ('dbo.Players') IS NOT NULL
    DROP TABLE dbo.Players;
CREATE TABLE dbo.Players
(
      PlayerID INT
    , Win INT
    , Defeat INT
    , StandOff INT
    , CONSTRAINT PK_Players PRIMARY KEY CLUSTERED (PlayerID) ON [PRIMARY]
);
INSERT INTO dbo.Players (PlayerID, Win, Defeat, StandOff)
VALUES
    (1, 7,  6,  9),
    (2, 12, 5,  0),
    (3, 3,  11, 1);

有多種方法可以完成此任務，因此讓我們看一下下面建議的每個實現的執行計劃。為此，我們將使用 dbForge Studio for SQL Server中提供的SQL Profiler。

為了在每次執行查詢時自動接收執行計劃，我們需要切換到分析模式：

也可以在不開始執行查詢計劃的情況下獲取查詢計劃。為此，您需要運行Generate Execution Plan命令。

開始吧！

1. UNION ALL

以前，SQL Server沒有提供將列轉換為行的有效方法。因此，許多用戶選擇通過UNION ALL語句結合使用一組不同的列來從同一張表中進行多次讀取：

SELECT PlayerID, GameCount = Win, GameType = 'Win'
FROM dbo.Players
    UNION ALL
SELECT PlayerID, Defeat, 'Defeat'
FROM dbo.Players
    UNION ALL
SELECT PlayerID, StandOff, 'StandOff'
FROM dbo.Players

這種做法的關鍵缺點是讀取多個數據。發生這種情況是因為UNION ALL將為每個子查詢掃描一次行，這大大降低了查詢執行的效率。

當我們查看以下查詢的執行計劃時，很明顯：

2. UNPIVOT

將列轉換為行的最快方法之一絕對是使用UNPIVOT運算符，該運算符于2005年在SQL Server中引入。讓我們使用此SQL UNPIVOT語法簡化上一個查詢：

SELECT PlayerID, GameCount, GameType
FROM dbo.Players
UNPIVOT (
    GameCount FOR GameType IN (
        Win, Defeat, StandOff
    )
) unpvt

作為查詢執行的結果，我們得到以下執行計劃：

3.VALUES

需要考慮的另一點是完成給定任務的可能性，即使用VALUES語句將列轉換為行。

使用VALUES語句的查詢將如下所示：

SELECT t.*
FROM dbo.Players
CROSS APPLY (
    VALUES
          (PlayerID, Win,      'Win')
        , (PlayerID, Defeat,   'Defeat')
        , (PlayerID, StandOff, 'StandOff')
) t(PlayerID, GameCount, GameType)

此外，與UNPIVOT相比，執行計劃將更加簡單：

4.Dynamic SQL

使用動態SQL允許在不包含在主鍵中的列之間具有兼容的數據類型的情況下，為任何表創建通用查詢：

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 + ']'
    FROM sys.columns c WITH(NOLOCK)
    LEFT JOIN (
        SELECT i.[object_id], i.column_id
        FROM sys.index_columns i WITH(NOLOCK)
        WHERE i.index_id = 1
    ) i ON c.[object_id] = i.[object_id] AND c.column_id = i.column_id
    WHERE c.[object_id] = OBJECT_ID(@table_name)
        AND i.[object_id] IS NULL
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + '
    )
) unpiv'
 
PRINT @SQL
EXEC sys.sp_executesql @SQL

更多內容歡迎查看下一篇

了解更多產品信息或想要購買產品正版授權請點擊