使用T-SQL进行数据透视转换

Question

在 SQL Server 中如何使用 T-SQL 将列转换为行，与 SSIS 中的“Pivot 转换”任务类似。以下是一个示例表格：

SSIS 中有一个名为“Pivot 转换”的任务，可以将列变成行，但在 SQL Server 中如何使用 T-SQL 完成同样的任务？

这是我的示例表格：

location product qty
-----------------------
delhi     PEPSI   100
GURGAON   CAKE    200
NOIDA     APPLE   150
delhi     cake    250

使用SSIS工具进行基于位置（location）的数据透视转换，将setkey作为位置字段，product作为透视键。转换后的输出结果如下：

location pepsi cake apple
delhi     100 null null
GURGAON   null 200 null 
NOIDA     null null 150 
delhi     null 250  null

- vhadalgi

Answer 1

使用 PIVOT 表运算符，可以按如下方式操作表格：

SELECT *
FROM tablename
PIVOT
(
  MAX(qty)
  FOR product IN([pepsi], [cake], [apple])
) as p;

• SQL Fiddle演示

请注意：

• 我使用了MAX聚合函数与qty一起，如果您想得到总和，请改用SUM或其他聚合函数。

• 您必须手动将列的值写入到透视表中，如果想要动态实现而不是手动编写，则需要使用动态SQL进行操作。

就像这样：

DECLARE @cols AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);

select @cols = STUFF((SELECT distinct ',' +
                        QUOTENAME(product)
                      FROM tablename
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)') 
                        , 1, 1, '');

SELECT @query = 'SELECT *
FROM tablename
PIVOT
(
  MAX(qty)
  FOR product IN(' + @cols + ')) AS p;';

execute(@query);

• Updated SQL Fiddle Demo

- Mahmoud Gamal

---

+1 对于动态 SQL 示例。 - Dan

Answer 2

一个透视请求包括三个逻辑处理阶段，每个阶段都有相关的元素：

1. 分组阶段
2. 扩展阶段
3. 聚合阶段，具有关联的聚合元素和聚合函数。

因此，将这些阶段与您的要求映射起来：

1. 必须在“位置”上进行分组
2. 基于“产品”列值进行扩展，并使用最终列名称：“百事可乐”，“蛋糕”，“苹果”。
3. “数量”值要聚合以生成分组和扩展元素的相交值

将这些值放入标准透视语句中：

SELECT ...
FROM <source_table_or_table_expression>
PIVOT(<agg_func>(<aggregation_element>)
FOR <spreading_element>
IN (<list_of_target_columns>)) AS <result_table_alias>

您的查询变为：

select location ,[PEPSI], [CAKE],[APPLE]
from table1
pivot (sum(qty)
       for product
       in ( [PEPSI], [CAKE],[APPLE])) AS T

重要的是要注意，使用PIVOT运算符时不需要在查询中明确指定分组元素，从而省去了GROUP BY的需求。 PIVOT运算符隐式地确定了分组元素，即源表（或表达式）中未被指定为扩展元素或聚合元素的所有属性。因此，您必须确保PIVOT运算符的源表除了分组、扩展和聚合元素之外没有其他属性，以便在指定了扩展和聚合元素后，仅剩下您打算用作分组元素的那些属性。您可以通过不直接将PIVOT运算符应用于原始表，而是应用于仅包含表示旋转元素而不包含其他元素的表达式来实现这一点。

select location ,[PEPSI], [CAKE],[APPLE]
from (select location,product,qty 
      from table1 ) as SourceTable
      pivot (sum(qty)
           for product
           in ( [PEPSI], [CAKE],[APPLE])) AS T

希望这有助于更好地理解Pivot运算符！！

编辑：添加了Unpivot运算符的概念：

与Pivoting一样，Unpivoting也涉及三个逻辑阶段：

1. 产生副本 2. 提取元素 3. 消除不相关的相交记录

将这些值放入标准的Unpivot语句中：

SELECT ...
FROM <source_table_or_table_expression>
UNPIVOT(<target_col_to_hold_source_col_values>
FOR <target_col_to_hold_source_col_names> IN(<list_of_source_columns>)) AS
<result_table_alias>;

将这些阶段与您的需求进行映射：

1. <target_col_to_hold_source_col_values> = 将保存源列值的列的名称 例如：为了保存列值[百事可乐]，[蛋糕]，[苹果]即100,250，你希望只有一个列：Qty
2. <target_col_to_hold_source_col_names> = 将保存源列名称的列的名称 例如：为了保存列名[百事可乐]，[蛋糕]，[苹果]，你希望只有一个列：product
3. <list_of_source_columns> = 您感兴趣的源表中的列的名称 例如：[百事可乐]，[蛋糕]，[苹果]

您的查询应该是：

SELECT location,product,qty
FROM #temp
UNPIVOT(qty
        FOR product  
        IN([Pepsi],[Cake],[Apple])) AS U;

我已在临时表#temp中添加了来自上述透视语句的结果。

这里需要注意的重要点是：对透视表进行反透视操作无法将原始表格恢复，因为透视会由于聚合而导致详细信息的丢失。

- Deepshikha

---

一篇关于数据透视的非常好的文章，我希望我有更多的+1可以给你！ - ConstantineK