sql server:sql动态行转列(pivot) 例子

最新推荐文章于 2024-06-19 11:23:45 发布

iteye_14050

最新推荐文章于 2024-06-19 11:23:45 发布

阅读量678

点赞数

文章标签：数据库

本文展示了一个使用 SQL Server 2005 或 2008 版本进行复杂数据聚合操作的例子，通过动态 SQL 和 PIVOT 函数来实现多竞争对手折扣信息的汇总显示。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

declare @sCompetitorName varchar(MAX)='' select @sCompetitorName = @sCompetitorName + '[' + CompetitorName + '],' from Competitor if(LEN(@sCompetitorName)>1) set @sCompetitorName = LEFT(@sCompetitorName,len(@sCompetitorName)-1) --PRINT @sCompetitorName DECLARE @Headers VARCHAR(MAX) SELECT @Headers = COALESCE( @Headers + ',isnull([' + cast(CompetitorName as varchar) + '],0) as [' + cast(CompetitorName as varchar) + ']', 'isnull([' + cast(CompetitorName as varchar) + '],0) as [' + cast(CompetitorName as varchar) + ']' ) FROM Competitor --PRINT @Headers declare @sSQL VARCHAR(MAX)=' select b.CompetitorID,b.ID,' + @Headers + ' from ( select C.CompetitorID,C.CompetitorName,ISNULL(PCD.Discount,0) AS Discount,PCD.ID from Competitor C inner JOIN PitchCompetitorDiscount PCD ON PCD.CompetitorID=C.CompetitorID inner JOIN PitchDetail PD ON PD.ID=PCD.ID ) as a pivot ( MIN(Discount) FOR CompetitorName in ( ' + @sCompetitorName + ') ) as b ' EXEC(@sSQL)

要求sql server 环境：sql server 2005 或 sql server 2008

效果图如下：