彩票走势图

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

更多内容欢迎查看下一篇

了解更多产品信息或想要购买产品正版授权请点击