在 SQL Server 中使用“Pivot”将行转换为列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15931607/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Convert Rows to columns using 'Pivot' in SQL Server
提问by Lynn
I have read the stuff on MS pivot tables and I am still having problems getting this correct.
我已经阅读了 MS 数据透视表上的内容，但我仍然无法正确理解这一点。
I have a temp table that is being created, we will say that column 1 is a Store number, and column 2 is a week number and lastly column 3 is a total of some type. Also the Week numbers are dynamic, the store numbers are static.
我有一个正在创建的临时表，我们会说第 1 列是商店编号，第 2 列是周数，最后第 3 列是某种类型的总数。此外，周数是动态的，商店数是静态的。
Store Week xCount
------- ---- ------
102 1 96
101 1 138
105 1 37
109 1 59
101 2 282
102 2 212
105 2 78
109 2 97
105 3 60
102 3 123
101 3 220
109 3 87
I would like it to come out as a pivot table, like this:
我希望它以数据透视表的形式出现，如下所示：
Store 1 2 3 4 5 6....
-----
101 138 282 220
102 96 212 123
105 37
109
Store numbers down the side and weeks across the top.
将数字向下存储，将周数存储在顶部。
回答by Taryn
If you are using SQL Server 2005+, then you can use the PIVOTfunction to transform the data from rows into columns.
如果您使用的是 SQL Server 2005+，则可以使用该PIVOT函数将数据从行转换为列。
It sounds like you will need to use dynamic sql if the weeks are unknown but it is easier to see the correct code using a hard-coded version initially.
如果周数未知，听起来您将需要使用动态 sql，但最初使用硬编码版本更容易查看正确的代码。
First up, here are some quick table definitions and data for use:
首先，这里有一些快速的表定义和数据供使用：
CREATE TABLE #yt
(
[Store] int,
[Week] int,
[xCount] int
);
INSERT INTO #yt
(
[Store],
[Week], [xCount]
)
VALUES
(102, 1, 96),
(101, 1, 138),
(105, 1, 37),
(109, 1, 59),
(101, 2, 282),
(102, 2, 212),
(105, 2, 78),
(109, 2, 97),
(105, 3, 60),
(102, 3, 123),
(101, 3, 220),
(109, 3, 87);
If your values are known, then you will hard-code the query:
如果您的值已知，那么您将对查询进行硬编码：
select *
from
(
select store, week, xCount
from yt
) src
pivot
(
sum(xcount)
for week in ([1], [2], [3])
) piv;
See SQL Demo
参见SQL 演示
Then if you need to generate the week number dynamically, your code will be:
然后如果您需要动态生成周数，您的代码将是：
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(Week)
from yt
group by Week
order by Week
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT store,' + @cols + ' from
(
select store, week, xCount
from yt
) x
pivot
(
sum(xCount)
for week in (' + @cols + ')
) p '
execute(@query);
See SQL Demo.
请参阅SQL 演示。
The dynamic version, generates the list of weeknumbers that should be converted to columns. Both give the same result:
动态版本生成week应转换为列的数字列表。两者都给出相同的结果：
| STORE | 1 | 2 | 3 |
---------------------------
| 101 | 138 | 282 | 220 |
| 102 | 96 | 212 | 123 |
| 105 | 37 | 78 | 60 |
| 109 | 59 | 97 | 87 |
回答by Enkode
This is for dynamic # of weeks.
这是动态的周数。
Full example here:SQL Dynamic Pivot
完整示例：SQL Dynamic Pivot
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
--Get distinct values of the PIVOT Column
SELECT @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(Week)
FROM (SELECT DISTINCT Week FROM #StoreSales) AS Weeks
--Prepare the PIVOT query using the dynamic
SET @DynamicPivotQuery =
N'SELECT Store, ' + @ColumnName + '
FROM #StoreSales
PIVOT(SUM(xCount)
FOR Week IN (' + @ColumnName + ')) AS PVTTable'
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery
回答by Eric Barr
I've achieved the same thing before by using subqueries. So if your original table was called StoreCountsByWeek, and you had a separate table that listed the Store IDs, then it would look like this:
我以前通过使用子查询实现了同样的目的。因此，如果您的原始表名为 StoreCountsByWeek，并且您有一个单独的表列出了商店 ID，那么它看起来像这样：
SELECT StoreID,
Week1=(SELECT ISNULL(SUM(xCount),0) FROM StoreCountsByWeek WHERE StoreCountsByWeek.StoreID=Store.StoreID AND Week=1),
Week2=(SELECT ISNULL(SUM(xCount),0) FROM StoreCountsByWeek WHERE StoreCountsByWeek.StoreID=Store.StoreID AND Week=2),
Week3=(SELECT ISNULL(SUM(xCount),0) FROM StoreCountsByWeek WHERE StoreCountsByWeek.StoreID=Store.StoreID AND Week=3)
FROM Store
ORDER BY StoreID
One advantage to this method is that the syntax is more clear and it makes it easier to join to other tables to pull other fields into the results too.
这种方法的一个优点是语法更清晰，并且可以更轻松地连接到其他表以将其他字段也拉入结果中。
My anecdotal results are that running this query over a couple of thousand rows completed in less than one second, and I actually had 7 subqueries. But as noted in the comments, it is more computationally expensive to do it this way, so be careful about using this method if you expect it to run on large amounts of data .
我的轶事结果是，在不到一秒的时间内完成了超过几千行的查询，实际上我有 7 个子查询。但正如评论中所指出的，这样做的计算成本更高，因此如果您希望它在大量数据上运行，请谨慎使用此方法。
回答by Praveen Nambiar
回答by MelgoV
I'm writing an sp that could be useful for this purpose, basically this sp pivot any table and return a new table pivoted or return just the set of data, this is the way to execute it:
我正在编写一个可能对此有用的 sp，基本上这个 sp 透视任何表并返回一个新的透视表或仅返回数据集，这是执行它的方法：
Exec dbo.rs_pivot_table @schema=dbo,@table=table_name,@column=column_to_pivot,@agg='sum([column_to_agg]),avg([another_column_to_agg]),',
@sel_cols='column_to_select1,column_to_select2,column_to_select1',@new_table=returned_table_pivoted;
please notethat in the parameter @agg the column names must be with '['and the parameter must end with a comma ','
请注意，在参数@agg 中，列名必须带有'['，并且参数必须以逗号结尾','
SP
SP
Create Procedure [dbo].[rs_pivot_table]
@schema sysname=dbo,
@table sysname,
@column sysname,
@agg nvarchar(max),
@sel_cols varchar(max),
@new_table sysname,
@add_to_col_name sysname=null
As
--Exec dbo.rs_pivot_table dbo,##TEMPORAL1,tip_liq,'sum([val_liq]),sum([can_liq]),','cod_emp,cod_con,tip_liq',##TEMPORAL1PVT,'hola';
Begin
Declare @query varchar(max)='';
Declare @aggDet varchar(100);
Declare @opp_agg varchar(5);
Declare @col_agg varchar(100);
Declare @pivot_col sysname;
Declare @query_col_pvt varchar(max)='';
Declare @full_query_pivot varchar(max)='';
Declare @ind_tmpTbl int; --Indicador de tabla temporal 1=tabla temporal global 0=Tabla fisica
Create Table #pvt_column(
pivot_col varchar(100)
);
Declare @column_agg table(
opp_agg varchar(5),
col_agg varchar(100)
);
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@table) AND type in (N'U'))
Set @ind_tmpTbl=0;
ELSE IF OBJECT_ID('tempdb..'+ltrim(rtrim(@table))) IS NOT NULL
Set @ind_tmpTbl=1;
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@new_table) AND type in (N'U')) OR
OBJECT_ID('tempdb..'+ltrim(rtrim(@new_table))) IS NOT NULL
Begin
Set @query='DROP TABLE '+@new_table+'';
Exec (@query);
End;
Select @query='Select distinct '+@column+' From '+(case when @ind_tmpTbl=1 then 'tempdb.' else '' end)+@schema+'.'+@table+' where '+@column+' is not null;';
Print @query;
Insert into #pvt_column(pivot_col)
Exec (@query)
While charindex(',',@agg,1)>0
Begin
Select @aggDet=Substring(@agg,1,charindex(',',@agg,1)-1);
Insert Into @column_agg(opp_agg,col_agg)
Values(substring(@aggDet,1,charindex('(',@aggDet,1)-1),ltrim(rtrim(replace(substring(@aggDet,charindex('[',@aggDet,1),charindex(']',@aggDet,1)-4),')',''))));
Set @agg=Substring(@agg,charindex(',',@agg,1)+1,len(@agg))
End
Declare cur_agg cursor read_only forward_only local static for
Select
opp_agg,col_agg
from @column_agg;
Open cur_agg;
Fetch Next From cur_agg
Into @opp_agg,@col_agg;
While @@fetch_status=0
Begin
Declare cur_col cursor read_only forward_only local static for
Select
pivot_col
From #pvt_column;
Open cur_col;
Fetch Next From cur_col
Into @pivot_col;
While @@fetch_status=0
Begin
Select @query_col_pvt='isnull('+@opp_agg+'(case when '+@column+'='+quotename(@pivot_col,char(39))+' then '+@col_agg+
' else null end),0) as ['+lower(Replace(Replace(@opp_agg+'_'+convert(varchar(100),@pivot_col)+'_'+replace(replace(@col_agg,'[',''),']',''),' ',''),'&',''))+
(case when @add_to_col_name is null then space(0) else '_'+isnull(ltrim(rtrim(@add_to_col_name)),'') end)+']'
print @query_col_pvt
Select @full_query_pivot=@full_query_pivot+@query_col_pvt+', '
--print @full_query_pivot
Fetch Next From cur_col
Into @pivot_col;
End
Close cur_col;
Deallocate cur_col;
Fetch Next From cur_agg
Into @opp_agg,@col_agg;
End
Close cur_agg;
Deallocate cur_agg;
Select @full_query_pivot=substring(@full_query_pivot,1,len(@full_query_pivot)-1);
Select @query='Select '+@sel_cols+','+@full_query_pivot+' into '+@new_table+' From '+(case when @ind_tmpTbl=1 then 'tempdb.' else '' end)+
@schema+'.'+@table+' Group by '+@sel_cols+';';
print @query;
Exec (@query);
End;
GO
This is an example of execution:
这是一个执行示例：
Exec dbo.rs_pivot_table @schema=dbo,@table=##TEMPORAL1,@column=tip_liq,@agg='sum([val_liq]),avg([can_liq]),',@sel_cols='cod_emp,cod_con,tip_liq',@new_table=##TEMPORAL1PVT;
then Select * From ##TEMPORAL1PVTwould return:
然后Select * From ##TEMPORAL1PVT会返回：
回答by FarajDaoud
Here is a revision of @Tayrn answer above that might help you understand pivoting a little easier:
这是上面@Tayrn 答案的修订版，可以帮助您更轻松地理解旋转：
This may not be the best way to do this, but this is what helped me wrap my head around how to pivot tables.
这可能不是执行此操作的最佳方法，但这是帮助我了解如何旋转表格的原因。
ID = rows you want to pivot
ID = 要旋转的行
MY_KEY = the column you are selecting from your original table that contains the column names you want to pivot.
MY_KEY = 您从原始表中选择的列，其中包含要透视的列名。
VAL = the value you want returning under each column.
VAL = 要在每列下返回的值。
MAX(VAL) => Can be replaced with other aggregiate functions. SUM(VAL), MIN(VAL), ETC...
MAX(VAL) => 可以替换为其他聚合函数。SUM(VAL), MIN(VAL), 等等...
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(MY_KEY)
from yt
group by MY_KEY
order by MY_KEY ASC
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT ID,' + @cols + ' from
(
select ID, MY_KEY, VAL
from yt
) x
pivot
(
sum(VAL)
for MY_KEY in (' + @cols + ')
) p '
execute(@query);
回答by Davis Zhou
Just give you some idea how other databases solve this problem. DolphinDBalso has built-in support for pivoting and the sql looks much more intuitive and neat. It is as simple as specifying the key column (Store), pivoting column (Week), and the calculated metric (sum(xCount)).
只是给你一些其他数据库如何解决这个问题的想法。DolphinDB还内置了对旋转的支持，并且 sql 看起来更加直观和整洁。它就像指定键列 ( Store)、旋转列 ( Week) 和计算指标 ( sum(xCount)) 一样简单。
//prepare a 10-million-row table
n=10000000
t=table(rand(100, n) + 1 as Store, rand(54, n) + 1 as Week, rand(100, n) + 1 as xCount)
//use pivot clause to generate a pivoted table pivot_t
pivot_t = select sum(xCount) from t pivot by Store, Week
DolphinDB is a columnar high performance database. The calculation in the demo costs as low as 546 ms on a dell xps laptop (i7 cpu). To get more details, please refer to online DolphinDB manual https://www.dolphindb.com/help/index.html?pivotby.html
DolphinDB 是一个列式高性能数据库。在戴尔 xps 笔记本电脑 (i7 cpu) 上，演示中的计算成本低至 546 毫秒。如需了解更多详情，请参阅在线 DolphinDB 手册https://www.dolphindb.com/help/index.html?pivotby.html
回答by Muhammad Bilal
select * from (select name, ID from Empoyee) Visits
pivot(sum(ID) for name
in ([Emp1],
[Emp2],
[Emp3]
) ) as pivottable;
