Sort months in ascending order in a Pivot Table-CodePudding

Below is the table I have created and inserted values in it:

CREATE TABLE Purchases   
(PurchaseDate varchar(25),   
SoldAmount int)  
GO  
  
INSERT INTO Purchases VALUES ('2001-01-28', 325),  
              ('2001-02-28', 245),  
              ('2001-02-28', 587),  
              ('2001-03-28', 312),  
              ('2001-04-28', 325),  
              ('2001-05-28', 675),  
              ('2001-05-28', 228),  
              ('2001-06-28', 109),  
              ('2001-07-28', 905),  
              ('2001-07-28', 207),  
              ('2001-08-28', 503),  
              ('2001-08-28', 102),  
              ('2001-09-28', 504),  
              ('2001-09-28', 103),  
              ('2001-09-28', 542),  
              ('2001-10-28', 915),  
              ('2001-10-28', 755),  
              ('2001-11-28', 385),  
              ('2001-12-28', 285),  
              ('2002-01-28', 492),  
              ('2002-02-28', 286),  
              ('2002-02-28', 664),  
              ('2002-03-28', 883),  
              ('2002-04-28', 673),  
              ('2002-05-28', 200),  
              ('2002-05-28', 421),  
              ('2002-06-28', 642),  
              ('2002-07-28', 325),  
              ('2002-07-28', 789),  
              ('2002-08-28', 432),  
              ('2002-08-28', 432),  
              ('2002-09-28', 886),  
              ('2002-09-28', 310),  
              ('2002-09-28', 970),  
              ('2002-10-28', 297),  
              ('2002-10-28', 301),  
              ('2002-11-28', 570),  
              ('2002-12-28', 921)  
GO

Now the question is: Display the total sum of sales (SoldAmount) for each year by month

This is what I have done:

SELECT [Month],[2001],[2002]
FROM
(
 SELECT DATENAME(M,PurchaseDate) AS [Month],YEAR(PurchaseDate) AS [Year],SoldAmount
 FROM Purchases
) AS DataSource
PIVOT
(
 SUM(SoldAmount)
 FOR [Year] IN ([2001],[2002])
) AS Pivoting
ORDER BY [Month]

I got the following result:

Although I got the result correct, but the only issue is that the Month column is not sorted in ascending order despite adding ORDER BY [Month].

How can months be sorted in ascending order in a Pivot Table?

CodePudding user response：

You could add the month number to the pivot portion of the query, and then order using it:

SELECT [Month], [2001], [2002]
FROM
(
    SELECT DATENAME(M, PurchaseDate) AS [Month],
           MONTH(PurchaseDate) AS mn,
           YEAR(PurchaseDate) AS [Year],
           SoldAmount
    FROM Purchases
) AS DataSource
PIVOT
(
    SUM(SoldAmount)
    FOR [Year] IN ([2001],[2002])
) AS Pivoting
ORDER BY mn;

CodePudding user response：

ORDER BY MONTH('01-' Month '-2000')