SQL查询中如何将行数据转为固定列（航班航线转置示例）

前提说明 #

你当前的表中route字段为多值拼接存储，首先需要拆分多值为单行数据，再做行转列处理。以下是通用实现逻辑，适配大多数主流数据库：

实现步骤 #

• 步骤1：拆分route字段的顿号分隔值，每个航线单独生成一行，同时对同一个flight_id下的航线按拆分顺序生成1~6的序号
• 步骤2：按flight_id分组，将序号对应的值映射为route1~route6列，无值填充N/A

代码示例 #

通用写法（兼容所有支持CTE和窗口函数的数据库，无需依赖Pivot语法） #

WITH split_routes AS (
    -- 此处替换为你对应数据库的字符串拆分逻辑，拆分后生成每行一个航线，同时带排序序号rn
    SELECT 
        flight_id,
        拆分后的航线值 AS route_name,
        ROW_NUMBER() OVER(PARTITION BY flight_id ORDER BY 拆分顺序) AS rn
    FROM 你的表名
)
SELECT
    flight_id,
    MAX(CASE WHEN rn = 1 THEN route_name ELSE 'N/A' END) AS route1,
    MAX(CASE WHEN rn = 2 THEN route_name ELSE 'N/A' END) AS route2,
    MAX(CASE WHEN rn = 3 THEN route_name ELSE 'N/A' END) AS route3,
    MAX(CASE WHEN rn = 4 THEN route_name ELSE 'N/A' END) AS route4,
    MAX(CASE WHEN rn = 5 THEN route_name ELSE 'N/A' END) AS route5,
    MAX(CASE WHEN rn = 6 THEN route_name ELSE 'N/A' END) AS route6
FROM split_routes
GROUP BY flight_id
ORDER BY flight_id;

固定6列的场景下CASE WHEN写法兼容性更强，无需适配不同数据库的Pivot语法差异，如果你想使用Pivot语法实现，把上述CASE WHEN部分替换为对应数据库的Pivot语法即可。

各数据库字符串拆分参考 #

• MySQL 8.0+ 拆分逻辑：

-- 替换split_routes里的查询逻辑
SELECT 
    t.flight_id,
    SUBSTRING_INDEX(SUBSTRING_INDEX(t.route, '、', n.n), '、', -1) AS route_name,
    n.n AS rn
FROM 你的表名 t
JOIN (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6) n
ON n.n <= LENGTH(t.route) - LENGTH(REPLACE(t.route, '、', '')) + 1

• PostgreSQL 拆分逻辑：

-- 替换split_routes里的查询逻辑
SELECT 
    flight_id,
    unnest(string_to_array(route, '、')) AS route_name,
    ROW_NUMBER() OVER(PARTITION BY flight_id) AS rn
FROM 你的表名

• SQL Server 拆分逻辑：

-- 替换split_routes里的查询逻辑
SELECT 
    t.flight_id,
    s.value AS route_name,
    ROW_NUMBER() OVER(PARTITION BY t.flight_id ORDER BY (SELECT 0)) AS rn
FROM 你的表名 t
CROSS APPLY STRING_SPLIT(t.route, '、', 1) s

输出效果 #

按你提供的测试数据执行后，输出结果如下：

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