一个简单的交叉报表_行转列
--行转列小实例--创建测试表if object_id(N'test', N'U') is not null
drop table testgowith 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 PivotTablego--创建存储过程if exists(select name from sysobjects where name = 'usp_GetPivotInfo') drop proc usp_GetPivotInfogocreate proc usp_GetPivotInfoasdeclare @czlx varchar(500), @SQL varchar(2000)
select @czlx = stuff((select distinct ',[' + czlx + ']' from test for xml path ('')),1,1,'')--select @czlxset @SQL = 'select czy, {#} from test pivot(sum(num) for czlx in ({#})) as T';set @SQL = replace(@SQL, '{#}', @czlx);exec(@SQL);goexec usp_GetPivotInfo ;
交叉前
交叉后
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
