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

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

UNPIVOT是將列轉(zhuǎn)換為行的最佳方法嗎？

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

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

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

如果通過第一列“屏幕”旋轉(zhuǎn)它，我們將得到以下結(jié)果：

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

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；

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

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

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

現(xiàn)在，讓我們更多地討論T-SQL UNPIVOT轉(zhuǎn)換的不同實現(xiàn)。

假設我們有一個匯總表，其中包含有關每個玩家玩游戲的結(jié)果的數(shù)據(jù)。我們有一項任務將列轉(zhuǎn)換為行。

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);

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

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

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

開始吧！

1. UNION ALL

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

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

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

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

2. UNPIVOT

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

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

作為查詢執(zhí)行的結(jié)果，我們得到以下執(zhí)行計劃：

3.VALUES

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

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

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

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

4.Dynamic SQL

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

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

更多內(nèi)容歡迎查看下一篇

了解更多產(chǎn)品信息或想要購買產(chǎn)品正版授權(quán)請點擊