Pivot With Case For Two Groups


  

CREATE TABLE mytable(
   YEAR INTEGER  NOT NULL  
  ,ID   INTEGER  NOT NULL
  ,NBR  INTEGER  NOT NULL
  ,PT   INTEGER  NOT NULL
  ,AMT  INTEGER  NOT NULL
);
INSERT INTO mytable(YEAR,ID,NBR,PT,AMT) VALUES (2016,63,1,26,2000);
INSERT INTO mytable(YEAR,ID,NBR,PT,AMT) VALUES (2016,63,2,27,1000);
INSERT INTO mytable(YEAR,ID,NBR,PT,AMT) VALUES (2016,63,3,25,3000);
INSERT INTO mytable(YEAR,ID,NBR,PT,AMT) VALUES (2015,66,11,55,5000);
INSERT INTO mytable(YEAR,ID,NBR,PT,AMT) VALUES (2015,66,13,56,1000);
INSERT INTO mytable(YEAR,ID,NBR,PT,AMT) VALUES (2015,66,11,58,5000);
INSERT INTO mytable(YEAR,ID,NBR,PT,AMT) VALUES (2015,66,12,57,8000);
 

 Declare @sql as NVarchar(max)=null  
declare @ColumnHeaders NVARCHAR(MAX) 

;with mycte
as(
select *, row_number() Over(Partition by ID Order by PT) rn from mytable)

Select @ColumnHeaders = STUFF( (SELECT  ',' + 'MAX(CASE WHEN rn=' + Cast(rn as varchar(5))+ ' THEN PT else 0 end ) as ' + quotename('PT'+ Cast(rn as varchar(2)),'[')  + char(10)+char(13)
  FROM mycte 
  Group by rn
  Order by rn FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '') +
 ','+STUFF( (SELECT    ',' +'MAX(CASE WHEN rn=' + Cast(rn as varchar(5))+ ' THEN AMT else 0 end ) as ' + quotename('AMT'+ Cast(rn as varchar(2)),'[')  + char(10)+char(13)
 FROM mycte    Group by rn Order by rn  FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '') 
  +' ,SUM(AMT) as ''Total''';
 --print @ColumnHeaders
 
Set @sql  =' ;with mycte
as(
select *, row_number() Over(Partition by ID Order by PT) rn from mytable)
Select [Year],ID,'+   @ColumnHeaders + ' from mycte Group by [Year],ID ';
     
--print @sql
EXEC(@SQL)

 
 

drop table mytable

/*
Year	ID	PT1	PT2	PT3	PT4	AMT1	AMT2	AMT3	AMT4	Total
2016	63	25	26	27	0	3000	2000	1000	0	6000
2015	66	55	56	57	58	5000	1000	8000	5000	19000
*/

 

 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e41a7ac5-36e3-4772-b042-e45264c8e44d/sql-help?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