如何用SQL PIVOT将周度销售数据转为动态列（每行对应单个客户）

先修正原查询的逻辑问题 #

原WHERE子句存在逻辑歧义，客户ID的OR条件需要用括号包裹，否则会导致不符合日期条件的CustID=-2数据也被查询出来。修正后的基础查询如下：

SELECT
    custname, 
    CASE 
        WHEN date < '11/26/2023' THEN -1 
        ELSE DATEPART(wk, date) 
    END AS week_num, 
    SUM(amount) AS sales,
    COUNT(salesid) AS orders 
FROM 
    SalesTable 
INNER JOIN 
    CustomerTable c ON salestable.CustID = c.CustID
WHERE 
    date < '1/27/2024' 
    AND (c.CustID = 10285 OR c.CustID = -2)
GROUP BY 
    c.custid, custname, [address],
    CASE WHEN date < '11/26/2023' THEN -1 ELSE DATEPART(wk, date) END,
    CASE WHEN date < '11/26/2023' THEN '11/25/2023' ELSE DATEADD(dd,7-(DATEPART(dw, date)), date) END 
ORDER BY 
    1, 2

静态PIVOT实现（已知周编号范围） #

如果你的周编号是固定的（比如已知为-1、48、49、50、1、2、3），可以直接写静态PIVOT，将每个周的sales和orders分别转成列：

SELECT
    custname,
    [sales_-1], [orders_-1],
    [sales_48], [orders_48],
    [sales_49], [orders_49],
    [sales_50], [orders_50],
    [sales_1], [orders_1],
    [sales_2], [orders_2],
    [sales_3], [orders_3]
FROM (
    -- 将基础查询的sales和orders拆分成行，为PIVOT做准备
    SELECT
        custname,
        CONCAT('sales_', week_num) AS col_name,
        CAST(sales AS DECIMAL(18,2)) AS col_value
    FROM (
        -- 嵌入修正后的基础查询
        SELECT
            custname, 
            CASE 
                WHEN date < '11/26/2023' THEN -1 
                ELSE DATEPART(wk, date) 
            END AS week_num, 
            SUM(amount) AS sales,
            COUNT(salesid) AS orders 
        FROM 
            SalesTable 
        INNER JOIN 
            CustomerTable c ON salestable.CustID = c.CustID
        WHERE 
            date < '1/27/2024' 
            AND (c.CustID = 10285 OR c.CustID = -2)
        GROUP BY 
            c.custid, custname, [address],
            CASE WHEN date < '11/26/2023' THEN -1 ELSE DATEPART(wk, date) END,
            CASE WHEN date < '11/26/2023' THEN '11/25/2023' ELSE DATEADD(dd,7-(DATEPART(dw, date)), date) END 
    ) AS base_data
    UNION ALL
    SELECT
        custname,
        CONCAT('orders_', week_num) AS col_name,
        CAST(orders AS INT) AS col_value
    FROM (
        -- 同样嵌入修正后的基础查询
        SELECT
            custname, 
            CASE 
                WHEN date < '11/26/2023' THEN -1 
                ELSE DATEPART(wk, date) 
            END AS week_num, 
            SUM(amount) AS sales,
            COUNT(salesid) AS orders 
        FROM 
            SalesTable 
        INNER JOIN 
            CustomerTable c ON salestable.CustID = c.CustID
        WHERE 
            date < '1/27/2024' 
            AND (c.CustID = 10285 OR c.CustID = -2)
        GROUP BY 
            c.custid, custname, [address],
            CASE WHEN date < '11/26/2023' THEN -1 ELSE DATEPART(wk, date) END,
            CASE WHEN date < '11/26/2023' THEN '11/25/2023' ELSE DATEADD(dd,7-(DATEPART(dw, date)), date) END 
    ) AS base_data
) AS unpivoted_data
PIVOT (
    MAX(col_value)
    FOR col_name IN (
        [sales_-1], [orders_-1],
        [sales_48], [orders_48],
        [sales_49], [orders_49],
        [sales_50], [orders_50],
        [sales_1], [orders_1],
        [sales_2], [orders_2],
        [sales_3], [orders_3]
    )
) AS pivoted_data
ORDER BY custname;

动态PIVOT实现（自动适配所有周编号） #

如果周编号是动态变化的，需要用动态SQL自动生成列名：

DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX);

-- 生成所有需要的列名（sales_xxx和orders_xxx）
SELECT @cols = STRING_AGG(QUOTENAME(col_name), ', ')
FROM (
    SELECT DISTINCT CONCAT('sales_', week_num) AS col_name
    FROM (
        SELECT
            CASE 
                WHEN date < '11/26/2023' THEN -1 
                ELSE DATEPART(wk, date) 
            END AS week_num
        FROM SalesTable
        INNER JOIN CustomerTable c ON salestable.CustID = c.CustID
        WHERE date < '1/27/2024' AND (c.CustID = 10285 OR c.CustID = -2)
    ) AS week_nums
    UNION ALL
    SELECT DISTINCT CONCAT('orders_', week_num) AS col_name
    FROM (
        SELECT
            CASE 
                WHEN date < '11/26/2023' THEN -1 
                ELSE DATEPART(wk, date) 
            END AS week_num
        FROM SalesTable
        INNER JOIN CustomerTable c ON salestable.CustID = c.CustID
        WHERE date < '1/27/2024' AND (c.CustID = 10285 OR c.CustID = -2)
    ) AS week_nums
) AS all_cols
ORDER BY col_name;

-- 构建动态PIVOT查询
SET @query = N'
SELECT custname, ' + @cols + '
FROM (
    SELECT
        custname,
        CONCAT(''sales_'', week_num) AS col_name,
        CAST(sales AS DECIMAL(18,2)) AS col_value
    FROM (
        SELECT
            custname, 
            CASE 
                WHEN date < ''11/26/2023'' THEN -1 
                ELSE DATEPART(wk, date) 
            END AS week_num, 
            SUM(amount) AS sales,
            COUNT(salesid) AS orders 
        FROM 
            SalesTable 
        INNER JOIN 
            CustomerTable c ON salestable.CustID = c.CustID
        WHERE 
            date < ''1/27/2024'' 
            AND (c.CustID = 10285 OR c.CustID = -2)
        GROUP BY 
            c.custid, custname, [address],
            CASE WHEN date < ''11/26/2023'' THEN -1 ELSE DATEPART(wk, date) END,
            CASE WHEN date < ''11/26/2023'' THEN ''11/25/2023'' ELSE DATEADD(dd,7-(DATEPART(dw, date)), date) END 
    ) AS base_data
    UNION ALL
    SELECT
        custname,
        CONCAT(''orders_'', week_num) AS col_name,
        CAST(orders AS INT) AS col_value
    FROM (
        SELECT
            custname, 
            CASE 
                WHEN date < ''11/26/2023'' THEN -1 
                ELSE DATEPART(wk, date) 
            END AS week_num, 
            SUM(amount) AS sales,
            COUNT(salesid) AS orders 
        FROM 
            SalesTable 
        INNER JOIN 
            CustomerTable c ON salestable.CustID = c.CustID
        WHERE 
            date < ''1/27/2024'' 
            AND (c.CustID = 10285 OR c.CustID = -2)
        GROUP BY 
            c.custid, custname, [address],
            CASE WHEN date < ''11/26/2023'' THEN -1 ELSE DATEPART(wk, date) END,
            CASE WHEN date < ''11/26/2023'' THEN ''11/25/2023'' ELSE DATEADD(dd,7-(DATEPART(dw, date)), date) END 
    ) AS base_data
) AS unpivoted_data
PIVOT (
    MAX(col_value)
    FOR col_name IN (' + @cols + ')
) AS pivoted_data
ORDER BY custname;';

-- 执行动态查询
EXEC sp_executesql @query;

补充说明 #

1. 核心思路是先将原查询的sales和orders通过UNION ALL拆分成独立行，给每行加上带周编号的列标识（如sales_-1），再通过PIVOT转成列。
2. 静态PIVOT适合周编号固定的场景，动态PIVOT会自动扫描符合条件的所有周编号，生成对应列。
3. DATEPART(wk, date)的周数计算受服务器DATEFIRST设置影响，如需统一周起始日，可先执行SET DATEFIRST 1;（设置周一为一周第一天）。

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