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

點(diǎn)擊下載dbForge Studio for SQL Server最新試用版

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

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

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

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

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

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

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

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

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

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

假設(shè)我們有一個(gè)匯總表，其中包含有關(guān)每個(gè)玩家玩游戲的結(jié)果的數(shù)據(jù)。我們有一項(xiàng)任務(wù)將列轉(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);

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

為了在每次執(zhí)行查詢時(shí)自動(dòng)接收?qǐng)?zhí)行計(jì)劃，我們需要切換到分析模式：

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

開始吧！

1. UNION ALL

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

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

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

當(dāng)我們查看以下查詢的執(zhí)行計(jì)劃時(shí)，很明顯：

2. UNPIVOT

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

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

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

3.VALUES

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

使用VALUES語(yǔ)句的查詢將如下所示：

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

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

4.Dynamic SQL

使用動(dòng)態(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)品信息或想要購(gòu)買產(chǎn)品正版授權(quán)請(qǐng)點(diǎn)擊