How do I sort or order month in Dynamic Pivot in MMM/YYYY format

Question

I have the following tale

I could maintain the order of column with the help of following code

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(LOGDATE) 
               from #TEMP                    
               FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')

But how can I maintain the date in the format Dec/2013, Jan/2013, Feb/2013 ?

set @query = 'SELECT * from 
            (
                select name,logdate,value from #table                    
           ) x
            pivot 
            (
                 sum(value)
                for logdate in (' + @cols + ')
            ) p '

Answer 1

I got my own way to resolve this problem

create table #TABLE (name varchar(10), logdate datetime, value int)
insert #TABLE values 
('A', '2014-01-01', 200),
('B', '2014-01-01', 50),
('C', '2014-01-01', 25),
('A', '2014-02-01', 40),
('B', '2014-02-01', 66),
('C', '2014-02-01', 87),
('A', '2013-12-01', 97),
('B', '2013-12-01', 10),
('C', '2013-12-01', 4)

Now we select the column names in the format MMM/YYYY and the order is based on the logdate column.

DECLARE @cols NVARCHAR (MAX)

SELECT @cols = COALESCE (@cols + ',[' + LEFT(DATENAME(MONTH,logdate),3)+'/'+CAST(YEAR(logdate) AS
               VARCHAR(4)) + ']', 
              '[' + LEFT(DATENAME(MONTH,logdate),3)+'/'+CAST(YEAR(logdate) AS VARCHAR(4)) + ']')
               FROM    (SELECT DISTINCT logdate FROM #TABLE) PV  
               ORDER BY logdate

Now pivot dynamically with the new column and finally the columns are generated in perfect order in the format MMM/YYYY

Note: The logdate should be of the type datetime/date

DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT * FROM 
             (
                 SELECT name, LEFT(DATENAME(MONTH,logdate),3)+''/''+CAST(YEAR(logdate) AS     VARCHAR(4)) logdate, value FROM #TABLE
             ) x
             PIVOT 
             (
                 SUM(value)
                 FOR logdate IN (' + @cols + ')
            ) p;' 

EXEC SP_EXECUTESQL @query

Like this you can convert to any date formats and can maintain the order of date in pivoted columns by editing the conversion type in SELECT @cols and inner SELECT of Pivot statement.

Answer 2

You could try altering the string that is used for the column names and instead of QUOTENAME(LOGDATE) use this:

QUOTENAME(LEFT(DATENAME(MONTH,LOGDATE),3) + '/' + CAST(YEAR(LOGDATE) AS CHAR(4)))

This would give you columns like [Dec/2013],[Feb/2014],[Jan/2014]

UPDATE: working example with SQL Fiddle

SQL Fiddle

MS SQL Server 2008 Schema Setup:

create table pivot_test (name varchar(10), logdate datetime, value int)
insert pivot_test values 
('A', '2014-01-01', 200),
('B', '2014-01-01', 50),
('C', '2014-01-01', 25),
('A', '2014-02-01', 40),
('B', '2014-02-01', 66),
('C', '2014-02-01', 87),
('A', '2013-12-01', 97),
('B', '2013-12-01', 10),
('C', '2013-12-01', 4)

Query 1:

DECLARE @cols NVARCHAR(MAX)
SELECT @cols = STUFF((SELECT ',' + QUOTENAME(LEFT(DATENAME(MONTH,LOGDATE),3) + '/' + CAST(YEAR(LOGDATE) AS CHAR(4)))
               FROM (SELECT DISTINCT logdate FROM pivot_test) x ORDER BY logdate FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')

DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT * FROM 
             (
             SELECT name, LEFT(DATENAME(MONTH,LOGDATE),3) + ''/'' + CAST(YEAR(LOGDATE) AS CHAR(4)) AS custom_logdate, value FROM pivot_test
             ) x
             PIVOT 
             (
                 SUM(value)
                 FOR custom_logdate IN (' + @cols + ')
            ) p;' 

EXEC SP_EXECUTESQL @query

Results:

| NAME | DEC/2013 | JAN/2014 | FEB/2014 |
|------|----------|----------|----------|
|    A |       97 |      200 |       40 |
|    B |       10 |       50 |       66 |
|    C |        4 |       25 |       87 |