Dynamic UNPIVOT Sample-T-SQL



 CREATE TABLE test  (  [col1] INT,[col2] INT,[col3] INT,[col4] INT,[col5] INT  );

INSERT INTO test VALUES  ('1',  '11',  '9',  '5',  '4'),('7', '16', '10', '3','11'); 

 
select [col1],[col2],[col3],[col4],[col5], Row_Number() Over (Order By newid()) rn into #temp From test


Declare @sql as NVarchar(4000) 
Declare @Cols as NVarchar(4000)

Set @Cols=null

Select @Cols =  COALESCE(@Cols + ', ', '') + '('+ QUOTENAME(COLUMN_NAME)+')' 
FROM INFORMATION_SCHEMA.COLUMNS 
Where table_name='test' and TABLE_SCHEMA='dbo'
 
  

Select @sql='Select rn, Max(val) maxval from #temp t CROSS APPLY (Values ' + @Cols + ' )  d(val) Group By rn'

Print @sql;

exec (@sql)

 
 
Drop table #temp
Drop table test
 
 
 

 

ANother sample:




create table test (Client_ID INT, Data_DT VARCHAR(20), Amt_1 INT, Amt_2 INT, Amt_3 INT, Amt_4 INT, Amt_5 INT)

INSERT INTO test VALUES (1, '082014', 100, 0, 200, 300, 0)

select * from  test 


Declare @sql as NVarchar(4000) 
Declare @Cols as NVarchar(4000)=null 
 
Select @Cols =  COALESCE(@Cols + ', ', '') + '('+ QUOTENAME(COLUMN_NAME) + ', '+ QUOTENAME(COLUMN_NAME,'''') +')' 
FROM INFORMATION_SCHEMA.COLUMNS 
Where table_name='test' and TABLE_SCHEMA='dbo'
  
   --print @Cols
 
Select @sql='Select Client_ID , col, val   from test  t CROSS APPLY (Values ' + @Cols + ' )  d(val,col)  '
 
--Print @sql;
 
exec (@sql)


drop table test


 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s