SQL Server行转列实现需求及PIVOT语法错误求助

核心思路 #

要实现按_p分组、将_t行值转为列且缺失时段补0，关键是先构造所有_p与_t的笛卡尔积（确保每个分组下所有时段都存在），再关联原数据填充数值，最后通过条件聚合或PIVOT完成转列。

假设表结构与测试数据 #

假设你的表名为test_data，结构如下：

CREATE TABLE test_data (
    _p VARCHAR(50),  -- 分组字段
    _t VARCHAR(20),  -- 需转列的时段字段
    _q INT           -- 数值字段
);

测试数据示例：

INSERT INTO test_data VALUES
('GroupA', 'Morning', 10),
('GroupA', 'Afternoon', 15),
('GroupB', 'Morning', 8),
('GroupB', 'Evening', 12);

方案1：条件聚合（推荐，灵活易维护） #

条件聚合是最直接的实现方式，手动指定每个_t对应的列，用ISNULL补0：

SELECT
    _p,
    ISNULL(SUM(CASE WHEN _t = 'Morning' THEN _q END), 0) AS Morning,
    ISNULL(SUM(CASE WHEN _t = 'Afternoon' THEN _q END), 0) AS Afternoon,
    ISNULL(SUM(CASE WHEN _t = 'Evening' THEN _q END), 0) AS Evening
FROM (
    -- 生成所有_p和_t的组合
    SELECT DISTINCT d._p, t._t
    FROM test_data d
    CROSS JOIN (SELECT DISTINCT _t FROM test_data) t
) AS full_comb
LEFT JOIN test_data td ON full_comb._p = td._p AND full_comb._t = td._t
GROUP BY _p;

说明 #

1. 子查询full_comb生成所有分组与时段的组合，确保每个分组下不会缺失任何时段。
2. 左连接原数据后，用CASE语句匹配对应时段的_q值，ISNULL将缺失值转为0。

方案2：使用PIVOT语法 #

PIVOT本身不会自动补全缺失的分组-时段组合，所以需要先构造全量组合再转列：

SELECT
    _p,
    ISNULL(Morning, 0) AS Morning,
    ISNULL(Afternoon, 0) AS Afternoon,
    ISNULL(Evening, 0) AS Evening
FROM (
    -- 全量组合关联原数据
    SELECT full_comb._p, full_comb._t, td._q
    FROM (
        SELECT DISTINCT d._p, t._t
        FROM test_data d
        CROSS JOIN (SELECT DISTINCT _t FROM test_data) t
    ) AS full_comb
    LEFT JOIN test_data td ON full_comb._p = td._p AND full_comb._t = td._t
) AS source_data
PIVOT (
    SUM(_q)
    FOR _t IN ([Morning], [Afternoon], [Evening])
) AS pivot_result;

说明 #

1. 同样先通过笛卡尔积生成全量组合，左连接原数据后作为PIVOT的数据源。
2. PIVOT后用ISNULL将NULL值转为0，满足缺失时段补0的要求。

动态列扩展（如果_t值不固定） #

如果_t的取值是动态变化的，不能手动指定列名，可以用动态SQL实现：

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

-- 获取所有_t的 distinct 值，拼接为列名
SELECT @cols = STRING_AGG(QUOTENAME(_t), ', ')
FROM (SELECT DISTINCT _t FROM test_data) t;

-- 构造动态SQL
SET @sql = N'
SELECT
    _p,
    ' + STRING_AGG('ISNULL(' + QUOTENAME(_t) + ', 0) AS ' + QUOTENAME(_t), ', ') + '
FROM (
    SELECT full_comb._p, full_comb._t, td._q
    FROM (
        SELECT DISTINCT d._p, t._t
        FROM test_data d
        CROSS JOIN (SELECT DISTINCT _t FROM test_data) t
    ) AS full_comb
    LEFT JOIN test_data td ON full_comb._p = td._p AND full_comb._t = td._t
) AS source_data
PIVOT (
    SUM(_q)
    FOR _t IN (' + @cols + ')
) AS pivot_result;';

EXEC sp_executesql @sql;

说明 #

• 用STRING_AGG（SQL Server 2017+）拼接所有_t值为列名，自动适配动态变化的时段。
• 动态SQL会自动生成包含所有时段列的查询，无需手动修改。

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