Dynamic Pivot On Two Columns



Create table table1 (ID int, Date datetime, Test varchar(10))
INSERT INTO TABLE1 VALUES (81540,'2013-07-27 00:00:00.000', 'CT'), 
(81540,'2013-07-24 00:00:00.000', 'IMP'),
(81540,'2013-07-27 00:00:00.000', 'MRR'),
(81540,'2013-07-27 00:00:00.000', 'MRR'),
(81540,'2013-07-27 00:00:00.000', 'MRR2'),
(81541,'2013-07-24 00:00:00.000', 'IMP'),
(81541,'2013-07-27 00:00:00.000', 'ARR'),
(81541,'2013-07-27 00:00:00.000', 'BRR')
DECLARE @colTest AS NVARCHAR(4000)=''
DECLARE @colDate AS NVARCHAR(4000)=''
DECLARE @Cols AS NVARCHAR(4000)=''
DECLARE @sql AS NVARCHAR(max)

 ;With mycte as
 (Select distinct row_number() OVer(Partition By ID Order by Test, Date) rn from table1)
SELECT @colDate= (SELECT stuff( (SELECT ',['+ Cast(rn as varchar(10)) +']'FROM mycte  FOR XML PATH('')),1,1,''))
, @colTest= (SELECT stuff( (SELECT ',['+ Cast(rn+10 as varchar(10)) +']'FROM mycte  FOR XML PATH('')),1,1,''))
, @Cols= (SELECT stuff( (SELECT ', Max(['+ Cast(rn as varchar(10)) +']) as [Date' +Cast(rn as varchar(10))+']'+', Max(['+ Cast(rn+10 as varchar(10)) +']) as [Test' +Cast(rn as varchar(10))+']' FROM mycte  FOR XML PATH('')),1,1,''))
 


--print @colTest
--print @colDate
--print @Cols

--;with mycte1 as
--(Select *, row_number() OVer(Partition By ID Order by Test, Date) rn from table1)
--Select ID,  Max([1]) as Date1,  Max([11]) as Test1, Max([2]) as Date2,  Max([12]) as Test2, Max([3]) as Date3,  Max([13]) as Test3, Max([4]) as Date4
--, Max([14]) as Test4 from (SELECT ID, Date, Test, rn, rn+10 as rn10 from mycte1 ) src
--PIVOT (Max(Date) For rn IN ([1],[2],[3],[4])) pvt
--PIVOT (Max(Test) For rn10 IN ([11],[12],[13],[14])) pvt2
--GROUP BY ID
 

 
SET @sql=';with mycte1 as (Select *, row_number() OVer(Partition By ID Order by Test, Date) rn from table1) SELECT [ID], ' + @Cols 
+ ' FROM (SELECT ID, Date, Test, rn, rn+10 as rn10 from mycte1) src
 PIVOT (MAX(Date) FOR [rn] IN ('+ @colDate + ')) AS pvt
 PIVOT (MAX(Test) FOR [rn10] IN ('+ @colTest + ')) AS pvt10 
 Group By ID '
--print @sql
EXEC (@sql);

drop table table1




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