Dynamic UNPIVOT Gloabal Temp Table -T-SQL


  

 create table ##tempMSRCache (CompanyName varchar(50)
, Standard_A_Status varchar(50)
,Standard_A_Solution varchar(50)
, Standard_B_Status varchar(50)
, Standard_B_Solution varchar(50)
)

Insert into ##tempMSRCache values
('CompanyA','in spec','has backups','out of spec','pop mail'),
('CompanyB ','in spec','has backups','inspec','pop mail') 

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

Set @Cols=null

Select @Cols =  COALESCE(@Cols + ', ', '') + '('+QUOTENAME(COLUMN_NAME,'''')+',' + QUOTENAME(COLUMN_NAME)+')'
FROM  TempDB.[INFORMATION_SCHEMA].[COLUMNS]
  WHERE TABLE_NAME='##tempMSRCache' and COLUMN_NAME<>'CompanyName'

   --print @Cols

Select @sql='Select
CompanyName, Standard,Answer
FROM ##tempMSRCache t
CROSS APPLY (Values ' + @Cols + ' )  d(Standard,Answer) '

--Print @sql;

exec (@sql)

 Select * from ##tempMSRCache 

drop table ##tempMSRCache 

 

https://social.msdn.microsoft.com/Forums/en-US/beafb6f3-f162-4424-bec8-bd6bd6280459/dynamic-unpivot-with-unknown-number-of-rowscolumns?forum=transactsql

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