一个简单的交叉报表,
--行转列小实例 --创建测试表 if object_id(N'test', N'U') is not null drop table test go with PivotTable as ( select 'xxx' as czy, '点赞' as czlx, 2 as num union all select 'xxx', '浏览' as czlx, 14 as num union all select 'yyy', '浏览' as czlx, 10 as num union all select 'zzz', '浏览', 30 union all select 'zzz', '点赞', 3 ) select * into test from PivotTable go --创建存储过程 if exists(select name from sysobjects where name = 'usp_GetPivotInfo') drop proc usp_GetPivotInfo go create proc usp_GetPivotInfo as declare @czlx varchar(500), @SQL varchar(2000) select @czlx = stuff((select distinct ',[' + czlx + ']' from test for xml path ('')),1,1,'') --select @czlx set @SQL = 'select czy, {#} from test pivot(sum(num) for czlx in ({#})) as T'; set @SQL = replace(@SQL, '{#}', @czlx); exec(@SQL); go exec usp_GetPivotInfo ;
交叉前
交叉后
本站文章为和通数据库网友分享或者投稿,欢迎任何形式的转载,但请务必注明出处.
同时文章内容如有侵犯了您的权益,请联系QQ:970679559,我们会在尽快处理。