Dynamic Change Matrix (Unpivot and Pivot)


  

  CREATE TABLE inputs ([SampleNo] VARCHAR (50) NULL,[Col1] VARCHAR (50) NULL,[Col2] VARCHAR (50) NULL,[Col3] VARCHAR (50) NULL,[Col4] VARCHAR (50) NULL,[Col6] VARCHAR (50) NULL,[Col7] VARCHAR (50) NULL); 

INSERT INTO inputs ([SampleNo], [Col1], [Col2], [Col3], [Col4], [Col6], [Col7]) VALUES
('Sample 1', 'x.xx', 'xx.xx', 'x.xx', 'xx.xx', 'x.xx', 'xx.xx')
,('Sample 2', 'x.xx', 'xx.xx', 'x.xx', 'xx.xx', 'x.xx', 'xx.xx')

--select  * from inputs

Declare @sqlUnpivot as NVarchar(4000)
Declare @ColsUnpivot as NVarchar(4000)

Declare @sqlPivot as NVarchar(4000)
Declare @ColsPivot as NVarchar(4000)

Set @ColsUnpivot=null

Select @ColsUnpivot =  COALESCE(@ColsUnpivot + ', ', '') + '('+QUOTENAME(COLUMN_NAME,'''')+',' + QUOTENAME(COLUMN_NAME)+')'
FROM   [INFORMATION_SCHEMA].[COLUMNS]
WHERE TABLE_NAME='inputs' and COLUMN_NAME Like 'Col%'
   --print @ColsUnpivot

Set @ColsPivot=null
Set @ColsPivot = STUFF((Select DISTINCT ', ' + quotename(SampleNo ,']') FROM inputs FOR XML PATH (''),type).value('.','varchar(max)'), 1, 2, '')
   --print @@ColsPivot

--- drop temp table if it exists
 if object_id('tempMatrix','U') is not null
 drop table tempMatrix

 ----Unpivot query and load the result into a temp table

Set @sqlUnpivot='Select [SampleNo],Cols,Vals

into tempMatrix
FROM inputs t
CROSS APPLY (Values ' + @ColsUnpivot + ' )  d(Cols,Vals) '

--Print @sqlUnpivot;
exec (@sqlUnpivot)

Set @sqlPivot = 'Select * from tempMatrix Pivot (max(vals) for SampleNo in ('+ @ColsPivot + ')) pvt'

--Print @sqlPivot;
exec (@sqlPivot)

drop table inputs

---Another sample:
--  https://social.msdn.microsoft.com/Forums/sqlserver/en-US/bf52f787-3d17-4916-b6c8-f2337853b154/convert-column-into-row-and-row-in-column-in-sql-server-2008r2?forum=transactsql#d760866f-da81-486a-b2ed-0cfbf2e7f533

 
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