一个简单的交叉报表

  • Z时代
  • 2024-01-10
  • 分类：综合

database

--行转列小实例
--创建测试表
ifobject_id(N"test", N"U") isnotnull
droptable test
go
with PivotTable as
(
select"xxx"as czy, "点赞"as czlx, 2as num
unionall
select"xxx", "浏览"as czlx, 14as num
unionall
select"yyy", "浏览"as czlx, 10as num
unionall
select"zzz", "浏览", 30
unionall
select"zzz", "点赞", 3
)
select*into test from PivotTable
go
--创建存储过程
ifexists(select name from sysobjects where name ="usp_GetPivotInfo")
dropproc usp_GetPivotInfo
go
createproc usp_GetPivotInfo
as
declare@czlxvarchar(500),
@SQLvarchar(2000)        
select@czlx=stuff((selectdistinct",["+ 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 ;

 

以上是 一个简单的交叉报表 的全部内容， 来源链接： utcz.com/z/533127.html

回到顶部