SQL Server 2012：单列数据拆分至多列/多行的动态实现

嘿，我懂你碰到的痛点了——手动写214个列名的PIVOT语句简直是噩梦，而且一旦块大小有变动（哪怕现在固定214），维护起来也麻烦。之前用PIVOT没成功大概率是因为没结合动态列生成，或者没先给块内的行做编号对吧？

下面给你两种主流数据库的动态实现方案，完全不用手动输入列名：

第一步：给每个数据块内的行分配序号 #

首先得给同一个时间戳下的214行标上唯一序号（从1到214），这样后续PIVOT才能把每行映射到对应的列。用窗口函数ROW_NUMBER()就能搞定：

WITH numbered_blocks AS (
    SELECT
        timestamp,
        -- 替换成你实际要转成列的字段，比如你的三个数据列之一或全部
        variable_value,
        -- 按时间戳分组，给每组内的行编号
        ROW_NUMBER() OVER (PARTITION BY timestamp ORDER BY (SELECT NULL)) AS block_row_id
    FROM your_data_table
)

⚠️ 注意：如果你的数据块内的行有天然的顺序（比如有个variable_id字段），把ORDER BY (SELECT NULL)换成那个字段，保证序号对应固定的变量。

第二步：动态生成PIVOT语句 #

方案1：SQL Server 版本 #

用STRING_AGG拼接列名，然后执行动态SQL：

DECLARE @column_list NVARCHAR(MAX);
DECLARE @pivot_query NVARCHAR(MAX);

-- 动态生成列名，比如 col_1, col_2 ... col_214
SELECT @column_list = STRING_AGG(QUOTENAME('col_' + CAST(block_row_id AS VARCHAR(3))), ', ')
FROM (SELECT DISTINCT block_row_id FROM numbered_blocks) AS row_ids;

-- 组装PIVOT查询
SET @pivot_query = N'
SELECT timestamp, ' + @column_list + N'
FROM numbered_blocks
PIVOT (
    -- 因为每个block_row_id在同一timestamp下只有一个值，MAX/MIN都可以
    MAX(variable_value)
    FOR block_row_id IN (' + @column_list + N')
) AS pivoted_data;
';

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

方案2：MySQL 版本 #

MySQL没有STRING_AGG，用GROUP_CONCAT来拼接列名：

SET @column_list = (
    SELECT GROUP_CONCAT(DISTINCT CONCAT('`col_', block_row_id, '`'))
    FROM (
        SELECT
            ROW_NUMBER() OVER (PARTITION BY timestamp ORDER BY (SELECT NULL)) AS block_row_id
        FROM your_data_table
    ) AS row_ids
);

SET @pivot_query = CONCAT('
SELECT timestamp, ', @column_list, '
FROM (
    SELECT
        timestamp,
        variable_value,
        ROW_NUMBER() OVER (PARTITION BY timestamp ORDER BY (SELECT NULL)) AS block_row_id
    FROM your_data_table
) AS numbered_blocks
PIVOT (
    MAX(variable_value)
    FOR block_row_id IN (', @column_list, ')
) AS pivoted_data;
');

-- 执行动态查询
PREPARE stmt FROM @pivot_query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

如果想拆成行而不是列？ #

如果你的需求是把三个数据列拆成独立的行（比如把宽表转成窄表），用UNION ALL就能快速实现；要是列数太多，也可以用动态SQL生成UNION ALL语句，原理和上面的PIVOT类似——先查询表的列名，再自动拼接成完整语句：

-- 示例静态版（列数少的情况）
SELECT timestamp, 'col1' AS variable_name, col1 AS variable_value FROM your_data_table
UNION ALL
SELECT timestamp, 'col2' AS variable_name, col2 AS variable_value FROM your_data_table
UNION ALL
SELECT timestamp, 'col3' AS variable_name, col3 AS variable_value FROM your_data_table;

之前用PIVOT没成功的话，大概率是没先给块内的行编号，或者手动列名写错了。这个动态方案完全避开了手动输入列名的问题，而且不管块大小是214还是其他数值，都能自动适配。

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