Dynamic Pivot Table Sort by Month and year
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