一个简单的交叉报表_列转行

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

--列转行小实例
--创建测试表
ifobject_id(N"test", N"U") isnotnull
droptable test
go
with UnPivotTable as
(
select1as UserNO, "33"as A, "44"AS B, "55"as C
unionall
select2as UserNO, "23"as A, "34"AS B, "56"as C
)
select*into test from UnPivotTable
go
--创建存储过程
ifexists(select name from sysobjects where name ="usp_GetUnPivotInfo")
dropproc usp_GetUnPivotInfo
go
createproc usp_GetUnPivotInfo
as
declare@SQLnvarchar(4000)        
SELECT@SQL=isnull(@SQL+",","")+quotename(Name) FROM syscolumns
WHERE ID=object_id("test") and[name]notin ("UserNO") ORDERBY Colid
SET@SQL="select UserNO,[Attr],[value] from (select * from test) a unpivot ([value] for [Attr] in("+@SQL+"))b"
exec(@SQL);
go
exec usp_GetUnPivotInfo ;

交叉前

交叉后

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