UNPIVOT,PIVOT and PIVOT Dynamically



--Prepare sample tables
create table srcTable (Date datetime, [AC-1] decimal(8,4),  [AM-1] decimal(8,4), [AM-1E] decimal(8,4),[AM-1W] decimal(8,4), [AM-2] decimal(8,4))
Insert into srcTable (Date,[AC-1],[AM-1],[AM-1E]) values('1996-09-18', null, null, null),('1996-11-26', null, null, null),('1991-01-22', 26.45, null, null),('1991-01-28', 25.73, null, null)

create table targettable (Piezmeter varchar(10), TOC decimal(8,4), RedFlag decimal(8,4))
Insert into targettable values('AC-1',738.65, 720.00),('AM-1',723.31, 720.00),('AM-1E',722.7, 720.00), ('AM-1W',722.5, 720.00),('AM-2',723.3, 720.00)



;with mycte as (
Select col,val from (
select SUM([AC-1]) as [AC-1], SUM([AM-1]) as [AM-1], SUM([AM-1E]) as [AM-1E],SUM([AM-1W]) as [AM-1W], SUM([AM-2]) as [AM-2]  from srcTable) t
--UNPIVOT with cross apply
Cross apply (values('AC-1',[AC-1]),('AM-1', [AM-1]),('AM-1E', [AM-1E]),('AM-1W', [AM-1W]),('AM-2',[AM-2]) ) d(col, val)

)

--select Piezmeter, TOC-ISNULL(val,0) as val, RedFlag 
--from target  t Left join mycte m on t.Piezmeter=m.col


--PIVOT


Select [AC-1],[AM-1], [AM-1E], [AM-1W],[AM-2], RedFlag from ( 
select  Piezmeter, TOC-ISNULL(val,0) as val, RedFlag 
from targettable  t Left join mycte m on t.Piezmeter=m.col) s 
PIVOT (Max(val) For Piezmeter IN ([AC-1],[AM-1], [AM-1E], [AM-1W],[AM-2]) ) pvt


 

 ----Dynamic way
 
 --You need to modify the query to get the final PIVOT

DECLARE @col AS VARCHAR(max)=''
DECLARE @sql AS VARCHAR(max)
 
SELECT @col=stuff( (SELECT ',['+ Piezmeter  +']'
FROM targettable  
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'')
  ;
 
--print @col
 
SET @sql='SELECT [RedFlag], ' + @col
+ ' FROM (select  Piezmeter, TOC  as val, RedFlag from targettable  t  ) s PIVOT (MAX(val) FOR [Piezmeter] IN ('+ @col + ')) AS pvt'
--print @sql
EXEC (@sql)



--clean up
drop table srctable,targettable



 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?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