Dynamic Pivot with CASE for Dates


  
 
 create table sick_codes_Test$ (
 EMP_ID int, EMP_SHORT_NAME varchar(100), SEG_CODE varchar(10), 
 NOM_DATE date)
 Insert into sick_codes_Test$ values 
 (1,'aaa','s','2018-07-17'),
  (1,'aaa','s','2018-07-18'),
   (1,'aaa','s','2018-07-19'),
    (1,'aaa','s','2018-07-20'),
	 (1,'aaa','s','2018-07-21'),
	  (1,'aaa','s','2018-07-22'),
	   (1,'aaa','s','2018-07-23')
	   ,(2,'bbb','s2','2018-07-22'),
	   (2,'bbb','s2','2018-07-23')
declare @ColumnHeaders NVARCHAR(4000) ;
declare  @sql NVARCHAR(4000);
declare @Param int=7

-- --===== 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
,dates as (
Select n, DATEADD(day, -n+1, getdate() ) dt  
from(Select n from nums) D(n)
)
  
Select @ColumnHeaders = STUFF( (SELECT ',' + 'max (Case when NOM_DATE='+ quotename( Convert(char(8),dt,112),'''') +' then  SEG_CODE  else null end) as ' + Quotename(Convert(char(10),dt,120),'[')  + char(10)+char(13)
FROM  dates
Order by n
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
  
 --print @ColumnHeaders

	  SET @sql = N'Select EMP_ID, EMP_SHORT_NAME, '
+ @ColumnHeaders 
+' from sick_codes_Test$
Group by EMP_ID, EMP_SHORT_NAME ' 

--print @sql
 EXEC sp_executesql @sql;
  

  drop table sick_codes_Test$


 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1a77fecf-75a7-43ec-90fa-2b33888633c2/how-to-use-dynamic-dates-in-sql-pivot?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 )

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 )

Connecting to %s