SQL Server中如何对Receipts表查询结果进行Pivot行转列并汇总计数与营收

嗨，看起来你已经摸到门道啦！你之前的尝试已经实现了计数的行转列，但问题在于你需要同时汇总销售数量和营收金额两个指标——SQL的PIVOT一次只能处理一个聚合函数，所以单纯一次Pivot没法同时搞定这俩。下面给你两种可行的解决方案，按需选择：

方案一：用条件聚合实现（推荐，更灵活直观） #

这种方法其实是手动模拟行转列的逻辑，能同时处理多个聚合指标，代码可读性也很高：

SELECT
    [Name],
    -- 各月份销售数量汇总
    SUM(CASE WHEN DATENAME(Month, [Date]) = 'September' THEN 1 ELSE 0 END) AS September_Count,
    SUM(CASE WHEN DATENAME(Month, [Date]) = 'October' THEN 1 ELSE 0 END) AS October_Count,
    SUM(CASE WHEN DATENAME(Month, [Date]) = 'November' THEN 1 ELSE 0 END) AS November_Count,
    SUM(CASE WHEN DATENAME(Month, [Date]) = 'December' THEN 1 ELSE 0 END) AS December_Count,
    -- 各月份营收金额汇总（格式化为带$的字符串）
    '$' + CAST(SUM(CASE WHEN DATENAME(Month, [Date]) = 'September' THEN Total_Tax_Exclusive_Price ELSE 0 END) AS VARCHAR(15)) AS September_Revenue,
    '$' + CAST(SUM(CASE WHEN DATENAME(Month, [Date]) = 'October' THEN Total_Tax_Exclusive_Price ELSE 0 END) AS VARCHAR(15)) AS October_Revenue,
    '$' + CAST(SUM(CASE WHEN DATENAME(Month, [Date]) = 'November' THEN Total_Tax_Exclusive_Price ELSE 0 END) AS VARCHAR(15)) AS November_Revenue,
    '$' + CAST(SUM(CASE WHEN DATENAME(Month, [Date]) = 'December' THEN Total_Tax_Exclusive_Price ELSE 0 END) AS VARCHAR(15)) AS December_Revenue
FROM dbo.Receipts
WHERE
    [Name] LIKE '%coffee Cake%'
    AND [Date] BETWEEN '2022-09-15' AND '2022-12-20'
GROUP BY [Name]

原理很简单：用CASE WHEN判断每条记录所属的月份，分别对数量（每条记录算1）和金额做求和，最终把每个月份的结果转成单独的列。

方案二：用UNPIVOT+PIVOT组合实现 #

如果你坚持想用PIVOT语法，那得先把两个指标拆成行，再统一转列：

WITH ReceiptsAgg AS (
    -- 先准备基础数据：每条记录的月份、名称、数量（固定为1）、营收
    SELECT
        [Name],
        DATENAME(Month, [Date]) AS MonthName,
        1 AS ItemCount,
        Total_Tax_Exclusive_Price AS Revenue
    FROM dbo.Receipts
    WHERE
        [Name] LIKE '%coffee Cake%'
        AND [Date] BETWEEN '2022-09-15' AND '2022-12-20'
),
Unpivoted AS (
    -- 把数量和营收转成两行，让每个月份对应两个指标行
    SELECT
        [Name],
        MonthName,
        MetricValue,
        MetricType
    FROM ReceiptsAgg
    UNPIVOT (
        MetricValue FOR MetricType IN (ItemCount, Revenue)
    ) AS up
)
-- 最后把月份转成列，按指标类型汇总
SELECT
    [Name],
    MetricType,
    -- 格式化营收为带$的字符串，数量直接显示数字
    [September] = CASE WHEN MetricType = 'Revenue' THEN '$' + CAST([September] AS VARCHAR(15)) ELSE CAST([September] AS VARCHAR(15)) END,
    [October] = CASE WHEN MetricType = 'Revenue' THEN '$' + CAST([October] AS VARCHAR(15)) ELSE CAST([October] AS VARCHAR(15)) END,
    [November] = CASE WHEN MetricType = 'Revenue' THEN '$' + CAST([November] AS VARCHAR(15)) ELSE CAST([November] AS VARCHAR(15)) END,
    [December] = CASE WHEN MetricType = 'Revenue' THEN '$' + CAST([December] AS VARCHAR(15)) ELSE CAST([December] AS VARCHAR(15)) END
FROM Unpivoted
PIVOT (
    SUM(MetricValue)
    FOR MonthName IN ([September], [October], [November], [December])
) AS pvt

这个方法先通过UNPIVOT把两个指标（数量、营收）拆成独立的行，再用PIVOT把月份转成列，最后再格式化营收的显示格式。

顺便帮你排查下之前的错误 #

1. 你第一次写的查询里，SELECT子句直接写[name] Like '%coffee Cake%'是语法错误——LIKE只能用在WHERE子句里筛选数据，不能直接出现在查询字段里；
2. 第二次的查询只做了Count(Name)的Pivot，没有处理营收金额，所以才会缺少你需要的数值。

备注：内容来源于stack exchange，提问作者Derek