Dynamic Pivot Sample Code



Create Table Projects(Period varchar(100),ProjectName varchar(500),ProjectStatus Varchar(100),ProjectPercent varchar(100))
Insert Into Projects values('Jan2014','A','InProgress','80%')
Insert Into Projects values('Jan2014','B','Completed','100%')
Insert Into Projects values('Jan2014','C','InProgress','60%')
Insert Into Projects values('Mar2014','D','InProgress','90%')

declare @ColumnHeaders VARCHAR(MAX), @ColumnHeaders1 VARCHAR(MAX);
set @ColumnHeaders = STUFF( (SELECT DISTINCT ',' + quotename(Period,'[') 
                             FROM Projects
                             FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');

Set @ColumnHeaders1 = STUFF( (SELECT DISTINCT ',ISNULL(' + quotename(Period,'[') +',''No Records Found'') as ' +  quotename(Period,'[')
                             FROM Projects
                             FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = 
    'SELECT ' + @ColumnHeaders1 +' FROM
     (
         SELECT 
             [Period],
             ROW_NUMBER() OVER(PARTITION BY [Period] ORDER BY [Period]) as RN,
             [ProjectName] + ''-'' + [ProjectStatus] + ''-'' + [ProjectPercent] as ProjectInfo
         FROM Projects
     ) t
     PIVOT
     (
         MAX([ProjectInfo]) FOR [Period] IN (' + @ColumnHeaders + ')
     ) p';
print @sql
EXEC(@SQL)


drop table Projects
 


 
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