Devart数据库工具【教程】：UNPIVOT是将列转换为行的最佳方法吗？（上）

在dbForge Studio for SQL Server中借助SQL Profiler对执行计划进行分步分析，可以检测查询性能的瓶颈。

dbForge Studio for SQL Server为有效的探索、分析SQL Server数据库中的大型数据集提供全面的解决方案，并设计各种报表以帮助作出合理的决策。

dbForge Studio for SQL Server最新试用版

UNPIVOT是将列转换为行的最佳方法吗/strong>

首先，让我们给出定义并突出显示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 ALLSELECT PlayerID, Defeat, 'Defeat'FROM dbo.Players    UNION ALLSELECT PlayerID, StandOff, 'StandOff'FROM dbo.Players

这种做法的关键缺点是读取多个数据。发生这种情况是因为UNION ALL将为每个子查询扫描一次行，这大大降低了查询执行的效率。

当我们查看以下查询的执行计划时，很明显：

2. UNPIVOT

将列转换为行的最快方法之一绝对是使用UNPIVOT运算符，该运算符于2005年在SQL Server中引入。让我们使用此SQL UNPIVOT语法简化上一个查询：

SELECT PlayerID, GameCount, GameTypeFROM dbo.PlayersUNPIVOT (    GameCount FOR GameType IN (        Win, Defeat, StandOff    )) unpvt

作为查询执行的结果，我们得到以下执行计划：

3.VALUES

需要考虑的另一点是完成给定任务的可能性，即使用VALUES语句将列转换为行。

使用VALUES语句的查询将如下所示：

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

此外，与UNPIVOT相比，执行计划将更加简单：

4.Dynamic SQL

使用动态SQL允许在不包含在主键中的列之间具有兼容的数据类型的情况下，为任何表创建通用查询：

DECLARE @table_name SYSNAMESELECT @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 @SQLEXEC sys.sp_executesql @SQL

更多内容欢迎查看下一篇

了解更多产品信息或想要购买产品正版授权请点击【咨询在线客服】

标签：

来源：慧都