Dynamic Column Name wIth PIVOT



  create table Sales_Imports (ProductCode int, Price int, [date] date)
insert into Sales_Imports values
(1,10,'1/1/2014'),(1,109,'2/1/2014'),(1,10,'1/1/2014'),(1,109,'12/1/2014'),
(2,10,'1/1/2014'),(2,109,'2/1/2014'),(2,10,'10/1/2014'),(2,109,'12/1/2014')


declare @Year varchar(4)='2014'

DECLARE @MaxCount INT, @SQL NVARCHAR(max) ,@i INT;


Set @MaxCount = 12 
SET @i = 0;
SET @SQL = '';
 
WHILE @i < @MaxCount
BEGIN
    SET @i = @i + 1;
    SELECT @SQL = @Sql + ', SUM(CASE WHEN datepart(mm,[Date]) = ' + cast(@i AS NVARCHAR(10)) + ' THEN  Price END) AS ' +Left(DateName(month,Dateadd(month,@i-1,0)) ,3)+@Year
END
 
SET @SQL = N' SELECT  ProductCode, datepart(mm,Date) MonthValue ' + @SQL 
+ N' FROM  Sales_Imports
 where datepart(yyyy,Date) ='+ @Year + N'group by  ProductCode, datepart(mm,Date)
 Order by ProductCode, datepart(mm,Date)';

--PRINT @SQL;

execute (@SQL);


drop table Sales_Imports

 
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