时间戳

首页 > 解决方案 > 在 Pivot SQL 表中排序月份

sql - 在 Pivot SQL 表中排序月份

问题描述

我正在尝试基于月份和年份进行 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

标签： sqlsql-serverpivot

解决方案

只需使用条件聚合：

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删除条件。

推荐阅读