Dynamic Pivot with CASE


  
  
 
 --Create table questions (Question  varchar(30), Answer    varchar(30),             Legend  varchar(30))

--Insert into questions values ('Q1','Y','2015'),('Q1','N','2016'),('Q2','#','2015')

-- --===== Create number table on-the-fly
--;WITH Num1 (n) AS (
--SELECT 1 as n
--UNION ALL SELECT n+1 as n
--FROM Num1 Where n <101), 
--Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num1
--WHere n<= @Param ) ---Control the total columns list
 
 Declare @tablename sysname='questions'
 Declare @sqlCase as NVarchar(4000) =null
Declare @sql as NVarchar(4000)=null

 
declare @ColumnHeaders VARCHAR(MAX), @ColumnHeaders1 VARCHAR(MAX);
set @ColumnHeaders = STUFF( (SELECT DISTINCT ',' + 'Max(CASE WHEN Legend=' + quotename(Legend,'''') + ' THEN Answer else null end ) as ' + quotename(Legend,'[')  + char(10)+char(13)
                             FROM questions
                             FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
  --print @ColumnHeaders

Set @sql  =' Select Question,'+   @ColumnHeaders + ' from Questions Group by Question ';
    
print @sql
EXEC(@SQL)
 


--Another sample:
USE [AdventureWorks2008R2]
GO

DECLARE @Sql NVARCHAR(4000) =null 
  
declare @ColumnHeaders NVARCHAR(4000) ;
set @ColumnHeaders = STUFF( (SELECT DISTINCT ',' + 'AVG(CASE WHEN DaysToManufacture =' + quotename(DaysToManufacture,'''') + ' THEN  StandardCost  else null end ) as ' + quotename(DaysToManufacture,'[')  + char(10)+char(13)
                             FROM  Production.Product
                             FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
  --print @ColumnHeaders
 
Set @sql  =N' Select  ''AverageCost'' AS Cost_Sorted_By_Production_Days, '+   @ColumnHeaders + ' from  Production.Product ';
     
--print @sql
EXEC(@SQL)



--another example:
Create table Capacity(Name char(1),Value int)
Insert into Capacity values('A',6),('B',6),('C',5)
Create table Completed (Name char(1), Date date, Value int)
Insert into Completed values('A','18 jan,2017', 5)
,('A','19 jan,2017', 3)
,('B','17 jan,2017', 4)
,('B','17 jan,2017', 4)
,('B','20 jan,2017', 2)

declare @startdate date='17 Jan 2017' 
declare @enddate date= '20 Jan 2017'


DECLARE @Sql NVARCHAR(4000) =null
   
declare @ColumnHeaders NVARCHAR(4000) ;
;with dates as (
Select dateadd(day,n ,@startdate ) dt 
from(values(0),(1),(2),(3),(4),(5),(6),(7)) D(n)
Where dateadd(day,n ,@startdate )<=@enddate)


--load date list to a temp table
Select dt 
into tempdates
from dates

Select @ColumnHeaders = STUFF( (SELECT DISTINCT ',' + 'max(CASE WHEN dt =' + quotename(dt,'''') + ' THEN  value1  else null end ) as ' 
+ quotename(Cast(datepart(day,dt) as varchar(2))+'Capacity','[')  + char(10)+char(13)
+
',' + 'isnull(max(CASE WHEN dt =' + quotename(dt,'''') + ' THEN  value2  else null end ),0) as ' 
+ quotename(Cast(datepart(day,dt) as varchar(2))+'Complete','[') + char(10)+char(13)
FROM  tempdates
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');

 --print @ColumnHeaders


;with  names as (
Select distinct Name from Capacity
)
,combs as (
Select Name, dt from names,tempdates)

,finaldataset as (
Select c0.name,c0.dt,c1.value as value1, c2.value as value2 from combs  c0 join Capacity c1 on c0.name=c1.name
left join  Completed c2 on c0.name=c2.name and c0.dt=c2.Date
)


Select * into finaldataset2
from finaldataset

Select @sql  =N' Select  name, '+   @ColumnHeaders + ' from  finaldataset2 Group by name';
      
--print @sql
EXEC(@SQL)

--cleanup
Drop table finaldataset2,tempdates

drop table Completed,Capacity



---Another sample
create table test (ID int,  yearCol int, code1 int, code2 int)
Insert into test values (1   ,2014,  11 ,  14),(1   ,2014,  11,   15),(1   ,2014  ,12 ,  16)
,(1   ,2015,  11 ,  14),(1   ,2015,  12,   15),(1,2015,13,16)
 

Declare @sqlCase as NVarchar(4000) =null
Declare @sql as NVarchar(4000)=null
 
  
declare @ColumnHeaders VARCHAR(MAX), @ColumnHeaders1 VARCHAR(MAX);
set @ColumnHeaders = STUFF( (SELECT DISTINCT ',' + 'Max(CASE WHEN rn2=' + Cast(rn2 as varchar(2)) + ' THEN code2 else null end ) as ' + 'code1_'+Cast(rn2 as varchar(2))  
+ char(10)+char(13)  +','+ 'Max(CASE WHEN rn2=' + Cast(rn2 as varchar(2)) + ' THEN code1 else null end ) as ' +  'code2_'+Cast(rn2 as varchar(2)) 
+ char(10)+char(13)
FROM (
Select id,yearcol,code1,code2, row_number() Over(Partition by ID,yearCol,rn  Order by Code2) rn2
From (
Select ID ,yearCol,code1,code2
,row_number() Over(Partition by ID,yearCol,code1,Code2  Order by Code2) rn   
from test) t
WHERE rn=1
) t1
 FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
---print @ColumnHeaders
 
Set @sql  =' ;with mycte as (
Select ID ,yearCol,code1,code2
,row_number() Over(Partition by ID,yearCol,code1,Code2  Order by Code2) rn   from test) 
,mycte1 as (
Select id,yearcol,code1,code2,
 row_number() Over(Partition by ID,yearCol,rn  Order by Code2) rn2
From mycte
WHERE rn=1
)
Select id,yearcol,'+   @ColumnHeaders + ' from mycte1
Group by  id,yearcol ';
     
print @sql
 
EXECUTE sp_executesql @sql 



 drop table test


--another with two set columns
CREATE TABLE Table1( ID INT ,[year] INT);
INSERT INTO Table1( ID, [year]) VALUES  (1,2015) ,(2,2016) ;
CREATE TABLE Table2( ID INT ,TXCode INT , Spcode INT   );
INSERT INTO Table2( ID, TXCode  ,Spcode) 
VALUES  (1, 11, 12) ,(1, 23,12)  ,(2, 88,22) ,(2,92,111);
 

--dynamic with case
DECLARE @Sql NVARCHAR(4000) =null
    
declare @ColumnHeaders NVARCHAR(4000) ;
set @ColumnHeaders = STUFF( (SELECT DISTINCT ',' + 'Max(CASE WHEN rn =' + quotename(rn,'''') + ' THEN  TXCode  else null end ) as ' + quotename('TXCode'+Cast(rn as varchar(10)),'[')  + char(10)+char(13)
                           +',' + 'Max(CASE WHEN rn =' + quotename(rn,'''') + ' THEN  Spcode  else null end ) as ' + quotename('Spcode'+Cast(rn as varchar(10)),'[')  + char(10)+char(13)
						     FROM  ( select t2.ID,t1.[year],t2.TXCode,t2.Spcode 
, Row_number() Over(Partition by t2.ID Order by t2.TXCode  ) rn 
from Table1 t1 join Table2 t2 on t1.ID=t2.ID) t
                             FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
  --print @ColumnHeaders
   
Set @sql  =N' ;with mycte as (
 select t2.ID,t1.[year],t2.TXCode,t2.Spcode 
, Row_number() Over(Partition by t2.ID Order by t2.TXCode  ) rn 
from Table1 t1 join Table2 t2 on t1.ID=t2.ID) 
Select   ID, [year], '+   @ColumnHeaders + ' from mycte Group by  ID, [year] ';
       
--print @sql
EXEC(@SQL)
  

drop table Table1,Table2

 
  
 CREATE TABLE test ( BENR VARCHAR(255) , ORGEAN VARCHAR(255) )
 INSERT INTO test values ('*0088041', '5060057720790'),('*0088041', '5060057721933'),('*0088041','8718104010208');
--static with case
;with mycte as (
select * , Row_number() Over(Partition by BENR Order by ORGEAN ) rn from test)

Select BENR, Max(Case when rn=1 then ORGEAN end) as  ORGEAN1
,Max(Case when rn=2 then ORGEAN end) as ORGEAN2,
Max(Case when rn=3 then ORGEAN end) ORGEAN3 from mycte
Group by BENR



--dynamic with case
DECLARE @Sql NVARCHAR(4000) =null
   
declare @ColumnHeaders NVARCHAR(4000) ;
set @ColumnHeaders = STUFF( (SELECT DISTINCT ',' + 'Max(CASE WHEN rn =' + quotename(rn,'''') + ' THEN  ORGEAN  else null end ) as ' + quotename(ORGEAN+Cast(rn as varchar(10)),'[')  + char(10)+char(13)
                             FROM  ( select * , Row_number() Over(Partition by BENR Order by ORGEAN ) rn from test) t
                             FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
  --print @ColumnHeaders
  
Set @sql  =N' ;with mycte as (
select * , Row_number() Over(Partition by BENR Order by ORGEAN ) rn from test) 
Select  BENR, '+   @ColumnHeaders + ' from mycte Group by BENR ';
      
--print @sql
EXEC(@SQL)
 



drop table test
 


 

https://social.msdn.microsoft.com/Forums/en-US/afafd877-7498-431d-892e-e22b825710e9/sql-pivot-help?forum=transactsql

https://social.msdn.microsoft.com/Forums/en-US/c1831f46-c5be-4dbd-98ab-23515179a274/transform-records?forum=transactsql

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9ef8cbe9-c64e-4d94-815b-97ed25b4de2b/pivot-with-characters?forum=transactsql

https://social.msdn.microsoft.com/Forums/en-US/93af8ff1-ad3b-488f-9134-1b987664257b/adding-extra-column?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