Dynamic Pivot with Column in a Sorted Order


--create table [dbo].[Report] (id int identity(1,1), [Type] char(1), ABC int, T_Date date,[Sum] int)
--Insert into [dbo].[Report] values
--('A',1,dateadd(week,19,'1/1/2014'), 10),
--('A',2,dateadd(week,18,'1/1/2014'), 10),
--('A',2,dateadd(week,21,'1/1/2014'), 10),
--('A',1,dateadd(week,20,'1/1/2014'), 10),
--('A',3,dateadd(week,22,'1/1/2014'), 10)

----drop table  [dbo].[Report]

DECLARE @columns NVARCHAR(MAX),@columns_pivot NVARCHAR(MAX), @sql NVARCHAR(MAX);

    SELECT @columns_pivot = COALESCE(@columns_pivot + ', ', '') + QUOTENAME(Week_No)
      ,@columns = COALESCE(@columns + ', ', '') + 'ISNULL(' + QUOTENAME(Week_No) + ',0) AS ' + QUOTENAME(Week_No) + ''
    FROM (SELECT DISTINCT DATEPART(wk,T_Date) As Week_No FROM [dbo].[Report]
	   WHERE DATEPART(m,T_Date) = 5) x
	   ORDER BY Week_No DESC; 

--print @columns_pivot
--print @columns
    SET @sql = '
    SELECT ABC, ' + @columns + '
    FROM   (Select  ABC ,SUM(CASE WHEN Type = ''A'' THEN Sum ELSE 0 END) AS Revenue
    ,DATEPART(wk,T_Date) As Week_No
     FROM [dbo].[Report]
     GROUP BY ABC
    ,DATEPART(wk,T_Date)
     ) As j 
     PIVOT(   max(Revenue)    FOR Week_No in (' + @columns_pivot + ')) As p '

	 print @sql
	-- exec(@sql)   

 

http://social.msdn.microsoft.com/Forums/en-US/049747df-9131-4aa9-9018-3ec4aac44daf/sql-server-sort-dynamic-pivot-column-names?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