在数据透视表中按升序对月份进行排序

Question

下面是我创建并在其中插入值的表：

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

现在的问题是： 按月显示每年的销售总额 (SoldAmount)

这就是我所做的：

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]

我得到以下结果：

虽然我得到的结果是正确的，但是唯一的问题是这个月尽管添加了 ORDER BY [Month]，但列并未按升序排序。

如何在数据透视表中按升序对月份进行排序？

Answer 1

您可以将月份数字添加到查询的数据透视部分，然后使用它进行排序：

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;

Answer 2

SELECT [Month],[2001],[2002] FROM ( SELECT DATENAME(M,PurchaseDate) AS [Month],YEAR(PurchaseDate) AS Year],SoldAmount FROM Purchases) AS ataSource PIVOT (SUM(SoldAmount) FOR [Year] IN ([2001],[2002])) AS Pivoting ORDER BY MONTH('01-'+Month+'-2000')

---