使用T-SQL进行数据透视转换 [英] Pivot transformation using t-sql
问题描述
在 SSIS
中有一个名为数据透视转换的任务,该任务将列转换为行,
,但是如何使用t-sql在sql服务器中执行相同的任务?
In SSIS
there is a task called Pivot transformation that changes columns into rows,
but how to do the same task in sql server using t-sql ?
这是我的示例表
location product qty
-----------------------
delhi PEPSI 100
GURGAON CAKE 200
NOIDA APPLE 150
delhi cake 250
因此,在使用ssis工具将枢轴转换的位置设置为setkey并将产品作为枢轴键后,o / p变成
so after the pivot transformation ON location as setkey and product as pivot key using ssis tool the o/p becomes
location pepsi cake apple
delhi 100 null null
GURGAON null 200 null
NOIDA null null 150
delhi null 250 null
推荐答案
使用 PIVOT
表运算符如下:
SELECT *
FROM tablename
PIVOT
(
MAX(qty)
FOR product IN([pepsi], [cake], [apple])
) as p;
- SQL小提琴演示
-
我使用了
MAX
具有数量
的聚合函数,如果要获取总和,请使用SUM
或任何其他聚合 I used the
MAX
aggregate function with theqty
, if you want to get the total sum useSUM
or any other aggregate function instead.
请注意:
您必须将列的值手动写入,如果要动态执行此操作而不是手动编写,则必须
You have to write the values of the column to pivoted manually, if you want to do this dynamically instead of writing them manually, you have to use dynamic sql to do so.
就像这样:
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);
- 更新的SQL Fiddle演示
这篇关于使用T-SQL进行数据透视转换的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!