Linux安全网 - Linux操作系统_Linux 命令_Linux教程_Linux黑客

绿色网站无广告
会员投稿 投稿指南 本期推荐:
搜索:
您的位置: Linux安全网 > Linux编程 > 数据库管理 > » 正文

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

来源: keenweiwei 分享至:
 
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

效果图如下:


Tags:
分享至:
最新图文资讯
1 2 3 4 5 6
验证码:点击我更换图片 理智评论文明上网,拒绝恶意谩骂 用户名:
关于我们 - 联系我们 - 广告服务 - 友情链接 - 网站地图 - 版权声明 - 发展历史