动态添加查询结果列：制造作业操作数据行内汇总需求

需求：将制造作业操作数据按JobNum汇总为单行记录，每个作业包含的多个操作需拼接为「操作名(实际工时/预估工时)」格式，列名统一为Operation，按操作顺序填充，空列留空。

原数据 #

期望输出 #

之前尝试的问题 #

使用PIVOT实现时，需预先定义所有操作作为列，导致每行存在大量NULL值，不符合需求：

解决方案 #

核心思路是先为每个JobNum下的操作生成序号，再通过条件聚合实现列转行，同时拼接目标字符串。

SQL Server 实现 #

WITH JobOperations AS (
    SELECT 
        JobNum,
        CONCAT(Operation, ' (', Hours, '/', Estimate, ')') AS OpText,
        -- 若有固定操作顺序，替换ORDER BY后的(SELECT NULL)为对应排序字段
        ROW_NUMBER() OVER (PARTITION BY JobNum ORDER BY (SELECT NULL)) AS OpSeq
    FROM YourTableName
)
SELECT 
    JobNum,
    MAX(CASE WHEN OpSeq = 1 THEN OpText END) AS Operation,
    MAX(CASE WHEN OpSeq = 2 THEN OpText END) AS Operation,
    MAX(CASE WHEN OpSeq = 3 THEN OpText END) AS Operation,
    MAX(CASE WHEN OpSeq = 4 THEN OpText END) AS Operation
FROM JobOperations
GROUP BY JobNum
ORDER BY JobNum;

MySQL 实现 #

支持动态根据最大操作数生成列：

-- 获取最大操作数
SET @max_ops = (SELECT COUNT(*) FROM (SELECT JobNum, COUNT(*) AS cnt FROM YourTableName GROUP BY JobNum) t ORDER BY cnt DESC LIMIT 1);

-- 动态拼接SQL语句
SET @sql = NULL;
SELECT GROUP_CONCAT(
    'MAX(CASE WHEN OpSeq = ', seq, ' THEN OpText END) AS Operation'
) INTO @sql
FROM (SELECT @row := @row + 1 AS seq FROM information_schema.tables, (SELECT @row := 0) r LIMIT @max_ops) t;

SET @sql = CONCAT('
WITH JobOperations AS (
    SELECT 
        JobNum,
        CONCAT(Operation, " (", Hours, "/", Estimate, ")") AS OpText,
        ROW_NUMBER() OVER (PARTITION BY JobNum ORDER BY NULL) AS OpSeq
    FROM YourTableName
)
SELECT JobNum, ', @sql, ' FROM JobOperations GROUP BY JobNum ORDER BY JobNum;
');

-- 执行动态SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

PostgreSQL 实现 #

静态版本（已知最大操作数为4）：

WITH JobOperations AS (
    SELECT 
        JobNum,
        CONCAT(Operation, ' (', Hours, '/', Estimate, ')') AS OpText,
        ROW_NUMBER() OVER (PARTITION BY JobNum ORDER BY NULL) AS OpSeq
    FROM YourTableName
)
SELECT 
    JobNum,
    MAX(CASE WHEN OpSeq = 1 THEN OpText END) AS "Operation",
    MAX(CASE WHEN OpSeq = 2 THEN OpText END) AS "Operation",
    MAX(CASE WHEN OpSeq = 3 THEN OpText END) AS "Operation",
    MAX(CASE WHEN OpSeq = 4 THEN OpText END) AS "Operation"
FROM JobOperations
GROUP BY JobNum
ORDER BY JobNum;

若需要动态生成列，可编写PL/pgSQL存储过程实现。

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