无需Pivot与Case When，实现带Group By的SQL表转置

原代码问题分析 #

你的查询无法得到预期结果，核心问题有三点：

1. 分组包含ProductCategoryName，导致每个日期每个类别生成独立行，无法在同一行展示所有类别数据。
2. 子查询中使用max(ProductCategoryName)逻辑错误，子查询无法直接引用外层聚合函数，且应该匹配具体类别名称而非最大值。
3. 子查询未关联产品类别表，仅查询SalesOrderDetail，导致无法正确筛选对应类别的数据。

替代实现方法 #

方法一：预聚合CTE+关联子查询 #

先通过CTE预计算每个日期和类别的LineTotal总和，再按日期分组，用子查询提取各列对应值：

WITH CategoryDailyTotals AS (
    SELECT
        sod.ModifiedDate,
        pc.ProductCategoryName,
        SUM(sod.LineTotal) AS TotalLine
    FROM SalesOrderDetail sod
    INNER JOIN Product p ON p.ProductID = sod.ProductID
    INNER JOIN ProductSubcategory ps ON ps.ProductSubcategoryID = p.ProductSubcategoryID
    INNER JOIN ProductCategory pc ON pc.ProductCategoryID = ps.ProductCategoryID
    GROUP BY sod.ModifiedDate, pc.ProductCategoryName
)
SELECT
    cd.ModifiedDate,
    Bikes = (SELECT TotalLine FROM CategoryDailyTotals WHERE ModifiedDate = cd.ModifiedDate AND ProductCategoryName = 'Bikes'),
    Components = (SELECT TotalLine FROM CategoryDailyTotals WHERE ModifiedDate = cd.ModifiedDate AND ProductCategoryName = 'Components'),
    Clothing = (SELECT TotalLine FROM CategoryDailyTotals WHERE ModifiedDate = cd.ModifiedDate AND ProductCategoryName = 'Clothing'),
    Accessories = (SELECT TotalLine FROM CategoryDailyTotals WHERE ModifiedDate = cd.ModifiedDate AND ProductCategoryName = 'Accessories')
FROM CategoryDailyTotals cd
GROUP BY cd.ModifiedDate
ORDER BY cd.ModifiedDate;

方法二：多表LEFT JOIN聚合结果 #

先提取所有唯一日期，再分别对每个类别做聚合，最后通过LEFT JOIN合并到日期主表：

WITH AllDates AS (
    SELECT DISTINCT ModifiedDate
    FROM SalesOrderDetail
),
BikeTotals AS (
    SELECT
        sod.ModifiedDate,
        SUM(sod.LineTotal) AS Bikes
    FROM SalesOrderDetail sod
    INNER JOIN Product p ON p.ProductID = sod.ProductID
    INNER JOIN ProductSubcategory ps ON ps.ProductSubcategoryID = p.ProductSubcategoryID
    INNER JOIN ProductCategory pc ON pc.ProductCategoryID = ps.ProductCategoryID
    WHERE pc.ProductCategoryName = 'Bikes'
    GROUP BY sod.ModifiedDate
),
ComponentTotals AS (
    SELECT
        sod.ModifiedDate,
        SUM(sod.LineTotal) AS Components
    FROM SalesOrderDetail sod
    INNER JOIN Product p ON p.ProductID = sod.ProductID
    INNER JOIN ProductSubcategory ps ON ps.ProductSubcategoryID = p.ProductSubcategoryID
    INNER JOIN ProductCategory pc ON pc.ProductCategoryID = ps.ProductCategoryID
    WHERE pc.ProductCategoryName = 'Components'
    GROUP BY sod.ModifiedDate
),
ClothingTotals AS (
    SELECT
        sod.ModifiedDate,
        SUM(sod.LineTotal) AS Clothing
    FROM SalesOrderDetail sod
    INNER JOIN Product p ON p.ProductID = sod.ProductID
    INNER JOIN ProductSubcategory ps ON ps.ProductSubcategoryID = p.ProductSubcategoryID
    INNER JOIN ProductCategory pc ON pc.ProductCategoryID = ps.ProductCategoryID
    WHERE pc.ProductCategoryName = 'Clothing'
    GROUP BY sod.ModifiedDate
),
AccessoryTotals AS (
    SELECT
        sod.ModifiedDate,
        SUM(sod.LineTotal) AS Accessories
    FROM SalesOrderDetail sod
    INNER JOIN Product p ON p.ProductID = sod.ProductID
    INNER JOIN ProductSubcategory ps ON ps.ProductSubcategoryID = p.ProductSubcategoryID
    INNER JOIN ProductCategory pc ON pc.ProductCategoryID = ps.ProductCategoryID
    WHERE pc.ProductCategoryName = 'Accessories'
    GROUP BY sod.ModifiedDate
)
SELECT
    ad.ModifiedDate,
    bt.Bikes,
    ct.Components,
    clt.Clothing,
    at.Accessories
FROM AllDates ad
LEFT JOIN BikeTotals bt ON ad.ModifiedDate = bt.ModifiedDate
LEFT JOIN ComponentTotals ct ON ad.ModifiedDate = ct.ModifiedDate
LEFT JOIN ClothingTotals clt ON ad.ModifiedDate = clt.ModifiedDate
LEFT JOIN AccessoryTotals at ON ad.ModifiedDate = at.ModifiedDate
ORDER BY ad.ModifiedDate;

方法三：临时表存储聚合结果 #

用临时表预存日期-类别总和，再通过子查询拼接列：

CREATE TABLE #CategoryTotals (
    ModifiedDate DATETIME,
    CategoryName VARCHAR(50),
    TotalLine NUMERIC(18,6)
)

INSERT INTO #CategoryTotals
SELECT
    sod.ModifiedDate,
    pc.ProductCategoryName,
    SUM(sod.LineTotal)
FROM SalesOrderDetail sod
INNER JOIN Product p ON p.ProductID = sod.ProductID
INNER JOIN ProductSubcategory ps ON ps.ProductSubcategoryID = p.ProductSubcategoryID
INNER JOIN ProductCategory pc ON pc.ProductCategoryID = ps.ProductCategoryID
GROUP BY sod.ModifiedDate, pc.ProductCategoryName

SELECT
    ModifiedDate,
    (SELECT TotalLine FROM #CategoryTotals WHERE ModifiedDate = t.ModifiedDate AND CategoryName = 'Bikes') AS Bikes,
    (SELECT TotalLine FROM #CategoryTotals WHERE ModifiedDate = t.ModifiedDate AND CategoryName = 'Components') AS Components,
    (SELECT TotalLine FROM #CategoryTotals WHERE ModifiedDate = t.ModifiedDate AND CategoryName = 'Clothing') AS Clothing,
    (SELECT TotalLine FROM #CategoryTotals WHERE ModifiedDate = t.ModifiedDate AND CategoryName = 'Accessories') AS Accessories
FROM #CategoryTotals t
GROUP BY ModifiedDate
ORDER BY ModifiedDate

DROP TABLE #CategoryTotals

内容的提问来源于stack exchange，提问作者shwars