--列转行小实例--创建测试表if object_id(N'test', N'U') is not null drop table testgowith UnPivotTable as( select 1 as UserNO, '33' as A, '44' AS B, '55' as C union all select 2 as UserNO, '23' as A, '34' AS B, '56' as C )select * into test from UnPivotTablego--创建存储过程if exists(select name from sysobjects where name = 'usp_GetUnPivotInfo') drop proc usp_GetUnPivotInfogocreate proc usp_GetUnPivotInfoasdeclare @SQL nvarchar(4000) SELECT @SQL=isnull(@SQL+',','')+quotename(Name) FROM syscolumnsWHERE ID=object_id('test') and [name] not in ('UserNO') ORDER BY ColidSET @SQL='select UserNO,[Attr],[value] from (select * from test) a unpivot ([value] for [Attr] in('+@SQL+'))b'exec(@SQL);goexec usp_GetUnPivotInfo ;
交叉前
交叉后