Dynamic Pivot Table Sort by Month and year

Question

I have used dynamic pivot query using the script below.

    select @cols = STUFF((SELECT ',' +
                        QUOTENAME(DATENAME(MONTH, CONVERT(DATETIME, createDate)))
                      FROM OSCL                        
    GROUP BY QUOTENAME(DATENAME(MONTH, CONVERT(DATETIME, createDate)))
     , DATEPART(MONTH, CONVERT(DATETIME, createDate))
    ORDER BY DATEPART(MONTH, CONVERT(DATETIME, createDate))        
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)') 
                        , 1, 1, '');

I am getting the following result.

Description   January   February  March April 
xxxxxx           44       20        0    12

I want the following

Description   January 2017   February2017   March 2017  April 2017 
xxxxxx           44             20              0          12

Could you please suggest the script for this.

Thanking you,

Hem

Answer 1

This is one option:

It uses a subquery to get the date values but notice I've moved the ORDER BY clause out of the subquery.

declare @dt table (mydate date);
insert into @dt values ('20170101'),('20170201'),('20170301'),('20170401'),('20170501'),('20170601'),('20170701'),('20170801');

declare @cols varchar(1000);


select @cols = STUFF((SELECT ',' + QUOTENAME(CONCAT(mn.[MonthName], '-', CAST(mn.[Year] AS VARCHAR(10))))
                      FROM (SELECT YEAR(mydate) [Year], MONTH(mydate) [Month], DATENAME(MONTH, mydate) AS [MonthName] 
                            FROM @dt 
                            GROUP BY YEAR(mydate), MONTH(mydate), DATENAME(MONTH, mydate)) mn
                ORDER BY [Year],[Month]
                      FOR XML PATH(''), TYPE
                      ).value('.', 'NVARCHAR(MAX)') , 1, 1, '');

select @cols;
GO

| (No column name)                                                                                          |
| :-------------------------------------------------------------------------------------------------------- |
| [January-2017],[February-2017],[March-2017],[April-2017],[May-2017],[June-2017],[July-2017],[August-2017] |

dbfiddle here

Answer 2

See if this works for you.

select @cols = STUFF((SELECT ',' +
                        QUOTENAME(DATENAME(MONTH, CONVERT(DATETIME, CreatedDateTime)) + ' ' + Cast(Year(CreatedDateTime) as varchar(4)))
                      FROM Customers_CVSTargetCab_AuditLog                        
    GROUP BY QUOTENAME(DATENAME(MONTH, CONVERT(DATETIME, CreatedDateTime)) + ' ' +  Cast(Year(CreatedDateTime) as varchar(4)))
     , DATEPART(MONTH, CONVERT(DATETIME, CreatedDateTime))
    ORDER BY DATEPART(MONTH, CONVERT(DATETIME, CreatedDateTime))        
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)') 
                        , 1, 1, '');