使用'Pivot'SQL Server

小开

最佳答案

如果您使用的是SQL Server 2005+，那么您可以使用PIVOT函数将数据从行转换为列。

听起来，如果星期是未知的，您将需要使用动态sql，但最初使用硬编码版本更容易看到正确的代码。

首先，这里有一些快速的表定义和数据:

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);

如果你的值是已知的，那么你将硬编码查询:

select *
from
(
select store, week, xCount
from yt
) src
pivot
(
sum(xcount)
for week in ([1], [2], [3])
) piv;

看到SQL演示

然后如果你需要动态生成周数，你的代码将是:

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);

看到SQL演示。

动态版本生成week数字列表，这些数字应该转换为列。两者都给出了相同的结果:

| STORE |   1 |   2 |   3 |
---------------------------
|   101 | 138 | 282 | 220 |
|   102 |  96 | 212 | 123 |
|   105 |  37 |  78 |  60 |
|   109 |  59 |  97 |  87 |

小开

你可以这样做:

SELECT *
FROM yourTable
PIVOT (MAX(xCount)
FOR Week in ([1],[2],[3],[4],[5],[6],[7])) AS pvt

< a href = " http://www.sqlfiddle.com/ !演示3/16fd1/1 noreferrer“rel = > < / >

小开

这是动态的# of weeks。

完整的例子:SQL动态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

小开

我以前通过使用子查询实现了同样的目的。所以如果你原来的表叫做sto叙述byweek，你有一个单独的表列出商店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

这种方法的一个优点是语法更清晰，并且更容易连接到其他表，将其他字段也拉到结果中。

我的轶事结果是，在不到一秒钟的时间内运行这个查询的几千行，实际上我有7个子查询。但是正如评论中所指出的，这样做的计算成本更高，所以如果您希望它在大量数据上运行，请谨慎使用此方法。

小开

select * from (select name, ID from Empoyee) Visits
pivot(sum(ID) for name
in ([Emp1],
[Emp2],
[Emp3]
) ) as pivottable;

小开

我写了一个sp，这个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;

参数@agg中的列名必须是'['，参数必须以逗号','结束

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

这是一个执行的例子:

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;

那么Select * From ##TEMPORAL1PVT将返回:

小开

以下是上面@Tayrn回答的修订版，可能会帮助你更容易理解旋转:

这可能不是最好的方法，但这帮助我理解了如何透视表。

ID =要主元的行数

MY_KEY =您要从原始表中选择的列，其中包含您想要进行主元转换的列名。

VAL =您希望在每列下返回的值。

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);

小开

只是给你一些其他数据库如何解决这个问题的想法。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是一个柱状的高性能数据库。演示中的计算成本在戴尔xps笔记本电脑(i7 cpu)上低至546毫秒。要获得更多详细信息，请参考在线DolphinDB手册https://www.dolphindb.com/help/index.html?pivotby.html

小开

Pivot是SQL操作符之一，用于将输出中的唯一数据从一列转换为多列。这也意味着将行转换为列(旋转表)。让我们考虑这张桌子，

如果我想根据每个客户的产品类型(扬声器、眼镜、耳机)过滤这些数据，那么使用Pivot操作符。

Select CustmerName, Speaker, Glass, Headset
from TblCustomer
Pivot
(
Sum(Price) for Product in ([Speaker],[Glass],[Headset])
) as PivotTable