A simple cross Report_ Column to row

Time:2021-2-12
--Column to row small instance
--Create test table
if object_id(N'test', N'U') is not null
  drop table test
go
with 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 UnPivotTable
go
--Create stored procedure
if exists(select name from sysobjects where name = 'usp_GetUnPivotInfo')
    drop proc usp_GetUnPivotInfo
go

create proc usp_GetUnPivotInfo
as
declare @SQL nvarchar(4000)        
SELECT @SQL=isnull(@SQL+',','')+quotename(Name) FROM syscolumns
WHERE ID=object_id('test') and [name] not in ('UserNO') ORDER BY Colid
SET @SQL='select UserNO,[Attr],[value] from (select * from test) a unpivot ([value] for [Attr] in('[email protected]+'))b'
exec(@SQL);
go

exec usp_GetUnPivotInfo ;

Before crossing

After crossing