Sorting month name in dynamic pivot

Question

I produced a dynamic pivot with the following query which work but the column (monthyear) are in alphabetical order but I want them in chronological order The monthyear column is derived using a function in SQL Server 2014

CREATE TABLE ##MyTable (Num VARCHAR(10),    StartDate DATE, [Types] VARCHAR(10))
INSERT INTO ##MyTable VALUES
('AA1','2016-01-01', 'Type1'),('AA2','2017-01-04', 'Type1'),('AA3','2016-01-04', 'Type1'),('AA4','2017-01-01', 'Type2'),
('AA5','2017-01-10', 'Type3'),('AA6','2016-01-02', 'Type1'),('AA7','2017-01-05', 'Type1'),('AA8','2016-01-12', 'Type1'),
('AA9','2016-01-06', 'Type1'),('AA10','2016-01-10', 'Type3'),('AA11','2017-01-11', 'Type1'),('AA12','2016-01-09', 'Type2'),
('AA13','2016-08-06', 'Type3'),('AA14','2017-01-02', 'Type1'),('AA15','2016-01-05', 'Type1'),('AA16','2017-01-07', 'Type1'),
('AA17','2016-01-04', 'Type1'),('AA18','2017-01-03', 'Type3'),('AA19','2017-01-01', 'Type1'),('AA20','2016-01-10', 'Type2'),
('AA21','2018-01-02', 'Type3'),('AA22','2017-01-10', 'Type1'),('AA23','2017-01-11', 'Type1'),('AA24','2017-01-12', 'Type1'),
('AA25','2017-01-09', 'Type1'),('AA26','2017-01-03', 'Type3'),('AA27','2016-01-07', 'Type1'),('AA28','2017-01-03', 'Type3'),
('AA29','2016-01-09', 'Type3'),('AA30','2017-10-12', 'Type1'),('AA31','2016-01-08', 'Type1'),('AA32','2017-01-10', 'Type1'),
('AA33','2016-01-04', 'Type1'),('AA34','2016-01-03', 'Type1'),('AA35','2018-01-01', 'Type3'),('AA36','2016-01-12', 'Type3'),
('AA37','2017-01-12', 'Type1'),('AA38','2016-01-05', 'Type1'),('AA39','2017-01-01', 'Type1'),('AA40','2017-01-12', 'Type3'),
('AA41','2017-01-07', 'Type1'),('AA42','2017-01-04', 'Type3'),('AA43','2018-01-03', 'Type1'),('AA44','2016-01-08', 'Type1'),
('AA45','2016-09-10', 'Type1'),('AA46','2016-01-11', 'Type3'),('AA47','2017-01-10', 'Type1'),('AA48','2017-01-08', 'Type1'),
('AA49','2017-01-08', 'Type1'),('AA50','2016-01-06', 'Type3'),('AA51','2016-02-08', 'Type3'),('AA52','2017-01-02', 'Type3'),
('AA53','2018-01-01', 'Type3'),('AA54','2016-01-05', 'Type3'),('AA55','2018-01-02', 'Type1'),('AA56','2018-01-01', 'Type1'),
('AA57','2017-01-10', 'Type1'),('AA58','2017-01-11', 'Type3'),('AA59','2018-01-03', 'Type3'),('AA60','2017-01-05', 'Type1'),
('AA61','2016-01-10', 'Type3'),('AA62','2017-01-08', 'Type3'),('AA63','2016-01-06', 'Type2'),('AA64','2017-01-05', 'Type3'),
('AA65','2018-01-01', 'Type3'),('AA66','2017-02-03', 'Type1'),('AA67','2016-01-12', 'Type1'),('AA68','2016-01-11', 'Type3'),
('AA69','2016-01-09', 'Type3'),('AA70','2017-01-12', 'Type2'),('AA71','2016-01-08', 'Type3'),('AA72','2016-01-10', 'Type1'),
('AA73','2017-01-05', 'Type3'),('AA74','2016-01-02', 'Type3'),('AA75','2016-01-12', 'Type3'),('AA76','2016-01-02', 'Type1'),
('AA77','2017-02-08', 'Type1'),('AA78','2016-01-12', 'Type3'),('AA79','2017-01-04', 'Type1'),('AA80','2018-01-01', 'Type2'),
('AA81','2016-01-08', 'Type3'),('AA82','2017-01-11', 'Type1'),('AA83','2017-01-05', 'Type1');

--  --  PIVOT
SELECT 
                Num,
                [Types],
                StartDate,
                FORMAT(StartDate,'MMM-yy')AS MonthYear
        INTO ##MyTable2
FROM ##MyTable
-------------------------------------------------------------------------------
DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(MonthYear) 
            FROM ##MyTable2 c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = ' SELECT * FROM (
                SELECT
                Num,
                [Types],
                MonthYear
                FROM ##MyTable2) AS PV
PIVOT
(
COUNT(Num) FOR [MonthYear] IN (' + @cols + ')
) AS PV1'

EXECUTE (@query);

DROP TABLE ##MyTable;
DROP TABLE ##MyTable2;

Current output

Types   Aug-16  Feb-16  Feb-17  Jan-16  Jan-17  Jan-18  Oct-17  Sep-16
Type1   0          0      2       16      22       3      1       1
Type2   0          0      0        3       2       1      0       0
Type3   1          1      0       14      11       5      0       0

Desired output

Types   Jan-16  Feb-16  Aug-16  Sep-16  Jan-17  Feb-17  Oct-17  Jan-18
Type1   16        0       0      1        22       2       1       3
Type2   3         0       0      0         2       0       0       1
Type3   14        1       1      0        11       0       0       5

Is there a way this can be achieved in sql ?

Roley

Please don't SHOUT – Scoff

Apologies guys, I did not mean to shout. – Roley

I edited out the shouting. – Sesame

@Sesame thanks for that – Roley

Answer 1

I'd use the following approach to sort the months in ascending order

Demo

SELECT 
            Num,
            [Types],
            StartDate,
            FORMAT(StartDate,'MMM-yy')AS MonthYear,
            CONVERT(INT,REPLACE(CONVERT(VARCHAR(7),StartDate),'-','')) MonthYearSort
    INTO ##MyTable2
    FROM ##MyTable

DECLARE @cols AS NVARCHAR(MAX)='',
        @query  AS NVARCHAR(MAX);


WITH T AS
(
SELECT TOP 100 PERCENT QUOTENAME(MonthYear) MonthYear  , MonthYearSort
FROM ##MyTable2
GROUP BY QUOTENAME(MonthYear)  , MonthYearSort
ORDER BY MonthYearSort
)

SELECT @cols += ','+ MonthYear 
FROM T
ORDER BY MonthYearSort
SET @cols = STUFF(@Cols ,1,1,'')


set @query = ' SELECT [Types], '+@cols+' FROM (
                SELECT
                Num,
                [Types],
                MonthYear
                FROM ##MyTable2) AS PV
PIVOT
(
COUNT(Num) FOR [MonthYear] IN (' + @cols + ')
) AS PV1

EXECUTE (@query);

Extrauterine

Answer 2

You can change the @cols query to:

SET @cols = STUFF((SELECT ',' + MAX(QUOTENAME(MonthYear))
            FROM ##MyTable2 c
            GROUP BY MONTH(StartDate), YEAR(StartDate) -- use group by instead of distinct
            ORDER BY YEAR(StartDate), MONTH(StartDate) -- use `order by` here    
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

This produces:

[Jan-16],[Feb-16],[Aug-16],[Sep-16],[Jan-17],[Feb-17],[Oct-17],[Jan-18]

Demo here

Edit: (thanks to @EzequielLópezPetrucci)

You should also use 'Types, ' + @cols instead of * in order to explicitly specify the column order. * doesn't guarantee that the ordinal position of each column returned by the SELECT will be the same as the position defined on table creation.

Woodshed

Will also need to change the * for the @cols in the SELECT. – Reis

@EzequielLópezPetrucci Sorry, I cannot see where the * is being used. – Woodshed

When he sets the value for @query (the final SELECT), he is doing a SELECT * which might or might not return the pivoted columns in order. It might be a good idea to select them explicitly, replacing the * with 'Types, ' + @cols. – Reis

@EzequielLópezPetrucci Now, I see what you mean. Very good remark! – Woodshed