Dynamic Unpivot and Pivot with Case


  

 
  
create table test (emp_id int 
,[17-Jul] decimal(6,2) 
,[17-Aug] decimal(6,2)
,[17-Sep] decimal(6,2) 
,[17-Oct] decimal(6,2) 
,[17-Nov] decimal(6,2) 
,[17-Dec] decimal(6,2)
,[18-Jan] decimal(6,2) 
,[18-Feb] decimal(6,2) 
,[18-Mar] decimal(6,2) 
,[18-Apr] decimal(6,2) 
,[18-May] decimal(6,2) 
,[18-Jun] decimal(6,2))

 Insert into test 
 values(597, 0,0,0,84.01,0,0,0,0,0,95.13,0,0)


 select * from test
 Declare @sqlUnpivot as NVarchar(4000)
  Declare @sqlPivot as NVarchar(4000)
Declare @ColsUnpivot as NVarchar(4000)=null
Declare @ColsPivot as NVarchar(4000)=null
 
 
 
Select @ColsUnpivot =  COALESCE(@ColsUnpivot + ', ', '') + '('+QUOTENAME(COLUMN_NAME,'''')+','  + QUOTENAME(COLUMN_NAME) +','+  Cast(ORDINAL_POSITION as varchar(3)) +')'
FROM   [INFORMATION_SCHEMA].[COLUMNS]
  WHERE TABLE_NAME='test' and COLUMN_NAME'emp_id'

  Select @ColsPivot =  COALESCE(@ColsPivot + ', ', '')  + QUOTENAME(COLUMN_NAME)  
FROM   [INFORMATION_SCHEMA].[COLUMNS]
  WHERE TABLE_NAME='test' and COLUMN_NAME'emp_id'
 
 
Select @sqlUnpivot='Select
emp_id,Col, Nullif(YY_MMM,0) YY_MMM, ORDINAL_POSITION
into temp
FROM test t
CROSS APPLY (Values ' + @ColsUnpivot + '  )  d(Col, YY_MMM,ORDINAL_POSITION) '
exec (@sqlUnpivot)
--print @sqlUnpivot
  
Select @ColsPivot = STUFF( (SELECT  ',' + 'Max(CASE WHEN Col=' + quotename(Col,'''') + ' THEN [YY_MMM] else 0 end ) as ' + quotename(Col,'[')  + char(10)+char(13)
                             FROM temp
							 order by ORDINAL_POSITION
                             FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
  --print @ColumnHeaders
 
Set @sqlPivot  =' Select emp_id,'+   @ColsPivot + ' from 
( SELECT emp_id, Col, ORDINAL_POSITION, 
CAST(SUBSTRING(MAX( CAST(ORDINAL_POSITION AS BINARY(4)) + CAST(Nullif(YY_MMM,0) AS BINARY(8)) )
OVER( ORDER BY ORDINAL_POSITION ASC ROWS UNBOUNDED PRECEDING ),5,8) AS Decimal(6,2) ) [YY_MMM]
FROM temp) t Group by emp_id    ';
     
--print @sqlPivot
EXEC(@sqlPivot)

--clean up temp table
 drop table temp


 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 )

Google+ photo

You are commenting using your Google+ 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 )

w

Connecting to %s