如何编写SQL将单列多行数据集按每5行分组转置为多列结构

核心实现逻辑 #

• 按实际业务要求的排序规则（自增主键、插入时间等）给所有行生成连续行号，避免无序列查询导致的分组错乱
• 基于行号计算分组标识，每连续5行归为同一组，同时标记每行在组内的位置（第1~第5位）
• 按分组标识聚合，通过条件判断将组内不同位置的值提取为独立列，完成行列转换

通用兼容写法（支持所有带窗口函数的数据库） #

假设原表名为single_col_data，存储单值的字段为val，用于排序的自增主键为id：

WITH numbered_rows AS (
    -- 生成连续行号，ORDER BY 后替换为实际排序字段
    SELECT 
        val,
        ROW_NUMBER() OVER (ORDER BY id) AS rn
    FROM single_col_data
),
grouped_rows AS (
    -- 计算组ID、组内位置
    SELECT
        val,
        FLOOR((rn - 1) / 5) AS group_id,
        (rn - 1) % 5 + 1 AS pos
    FROM numbered_rows
)
-- 聚合转置为宽表
SELECT
    MAX(CASE WHEN pos = 1 THEN val END) AS col_1,
    MAX(CASE WHEN pos = 2 THEN val END) AS col_2,
    MAX(CASE WHEN pos = 3 THEN val END) AS col_3,
    MAX(CASE WHEN pos = 4 THEN val END) AS col_4,
    MAX(CASE WHEN pos = 5 THEN val END) AS col_5
FROM grouped_rows
GROUP BY group_id
ORDER BY group_id;

不同数据库简化写法 #

SQL Server / Oracle 支持PIVOT语法 #

可省略手动编写CASE WHEN的步骤，直接使用内置透视语法：

WITH numbered_rows AS (
    SELECT 
        val,
        ROW_NUMBER() OVER (ORDER BY id) AS rn
    FROM single_col_data
)
SELECT [1] AS col_1, [2] AS col_2, [3] AS col_3, [4] AS col_4, [5] AS col_5
FROM (
    SELECT
        val,
        FLOOR((rn - 1) / 5) AS group_id,
        (rn - 1) % 5 + 1 AS pos
    FROM numbered_rows
) AS src
PIVOT (
    MAX(val) FOR pos IN ([1], [2], [3], [4], [5])
) AS pvt
ORDER BY group_id;

MySQL 5.x 无窗口函数兼容写法 #

通过用户变量模拟行号生成逻辑：

SET @row_num = 0;
SELECT
    MAX(CASE WHEN pos = 1 THEN val END) AS col_1,
    MAX(CASE WHEN pos = 2 THEN val END) AS col_2,
    MAX(CASE WHEN pos = 3 THEN val END) AS col_3,
    MAX(CASE WHEN pos = 4 THEN val END) AS col_4,
    MAX(CASE WHEN pos = 5 THEN val END) AS col_5
FROM (
    SELECT
        val,
        @row_num := @row_num + 1 AS rn,
        FLOOR((@row_num - 1) / 5) AS group_id,
        (@row_num - 1) % 5 + 1 AS pos
    FROM single_col_data
    ORDER BY id
) AS t
GROUP BY group_id
ORDER BY group_id;

注意事项 #

• 排序规则必须和实际数据的行顺序一致，若原数据有自定义排序要求，直接替换ORDER BY后的字段即可，否则会导致分组顺序错乱
• 若最后一组不足5行，对应缺失的列会返回NULL，符合常规数据处理预期
• 行号生成逻辑天然区分每一行的位置，即使原单列存在重复值也不需要额外去重

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