在透视SQL表中排序月份

Question

我正在尝试基于月份和年份进行PIVOT。总的来说，我总是被枢轴卡住。试图订购从1月到12月的月份，但总是从12月到1月开始。以下是我迄今为止所做的：

SELECT PvtMonth.TYear,
PvtMonth.December,
PvtMonth.November,
PvtMonth.October,
PvtMonth.September,
PvtMonth.August,
PvtMonth.July,
PvtMonth.June,
PvtMonth.May,
PvtMonth.April,
PvtMonth.March,
PvtMonth.February,
PvtMonth.January
FROM
(
SELECT DATENAME(YEAR, InvoiceDate) AS TYear,DATENAME(MONTH, InvoiceDate) AS TMonth, TrackingNo
FROM [MSM14].[dbo].[PVH_Global_Dash]
WHERE ReceiverAddress LIKE '%78 Mccullough%'
) AS P1
PIVOT
(
COUNT(TrackingNo)
FOR TMonth IN
(
[January],
[February],
[March],
[April],
[May],
[June],
[July],
[August],
[September],
[October],
[November],
[December]
)) AS PvtMonth

Answer 1

只需使用条件聚合：

SELECT YEAR(InvoiceDate) AS TYear,
SUM(CASE WHEN MONTH(InvoiceDate) = 1 THEN 1 ELSE 0 END) as Jan,
SUM(CASE WHEN MONTH(InvoiceDate) = 2 THEN 1 ELSE 0 END) as Feb,
. . . 
FROM [MSM14].[dbo].[PVH_Global_Dash]
WHERE ReceiverAddress LIKE '%78 Mccullough%' AND
TrackingNo IS NOT NULL
GROUP BY YEAR(InvoiceDate);

条件TrackingNo IS NOT NULL可能不是必需的，但您的版本正在计算非NULL值。如果它从来都不是NULL，只需删除该条件。