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

最新推荐文章于 2023-06-19 18:51:10 发布

原创最新推荐文章于 2023-06-19 18:51:10 发布 · 4.8k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#sql server #pivot #sql #join

Sql Server 专栏收录该内容

125 篇文章

订阅专栏

本文介绍了如何在 SQL Server 环境下，通过复杂的 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

效果图如下：