MySQL行值转列实现：将生产线号转为列展示缝纫机类型

你的问题核心是要把Line字段的不同值作为列，每个列下展示对应生产线的所有Machine_Type（包含重复值）。你之前的SQL语句无法得到预期结果，是因为它逐行生成结果，会产生大量NULL值，且未将同一生产线的记录按顺序对齐到同一行。

解决方案 #

1. 静态SQL（已知所有生产线编号） #

如果生产线编号固定，可以使用以下SQL：

SELECT
    MAX(CASE WHEN Line = 'L01' THEN Machine_Type END) AS `L01`,
    MAX(CASE WHEN Line = 'L02' THEN Machine_Type END) AS `L02`,
    MAX(CASE WHEN Line = 'L03' THEN Machine_Type END) AS `L03`,
    MAX(CASE WHEN Line = 'L04' THEN Machine_Type END) AS `L04`,
    MAX(CASE WHEN Line = 'L05' THEN Machine_Type END) AS `L05`
FROM (
    -- 给每个生产线内的记录生成自增行号
    SELECT 
        Machine_Type,
        Line,
        ROW_NUMBER() OVER (PARTITION BY Line ORDER BY ID) AS rn
    FROM dr_scan
    WHERE scan_freq=2
) t
GROUP BY rn
ORDER BY rn;

2. 动态SQL（自动适配所有生产线） #

如果生产线编号不固定，使用动态SQL自动生成所有列，无需手动编写每个生产线的CASE语句：

-- 设置GROUP_CONCAT长度限制，避免列过多时截断
SET group_concat_max_len = 1000000;

SET @sql = NULL;

-- 拼接所有生产线对应的CASE语句
SELECT GROUP_CONCAT(DISTINCT
    CONCAT(
        'MAX(CASE WHEN Line = ''', Line, ''' THEN Machine_Type END) AS `', Line, '`'
    )
) INTO @sql
FROM dr_scan
WHERE scan_freq=2;

-- 组装完整SQL语句
SET @sql = CONCAT('SELECT ', @sql, ' FROM (
    SELECT 
        Machine_Type,
        Line,
        ROW_NUMBER() OVER (PARTITION BY Line ORDER BY ID) AS rn
    FROM dr_scan
    WHERE scan_freq=2
) t GROUP BY rn ORDER BY rn');

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

3. 兼容MySQL 5.x版本（无ROW_NUMBER()函数） #

如果你的MySQL版本低于8.0，没有ROW_NUMBER()函数，使用变量生成行号：

SET group_concat_max_len = 1000000;

SET @sql = NULL;

SELECT GROUP_CONCAT(DISTINCT
    CONCAT(
        'MAX(CASE WHEN Line = ''', Line, ''' THEN Machine_Type END) AS `', Line, '`'
    )
) INTO @sql
FROM dr_scan
WHERE scan_freq=2;

SET @sql = CONCAT('SELECT ', @sql, ' FROM (
    SELECT 
        Machine_Type,
        Line,
        @rn := IF(@current_line = Line, @rn + 1, 1) AS rn,
        @current_line := Line
    FROM dr_scan, (SELECT @rn := 0, @current_line := '') vars
    WHERE scan_freq=2
    ORDER BY Line, ID
) t GROUP BY rn ORDER BY rn');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

原理说明 #

1. 生成行号：通过ROW_NUMBER() OVER (PARTITION BY Line ORDER BY ID)（或变量）给每个生产线内的记录按ID排序生成自增行号，确保同一生产线的记录按顺序排列。
2. 分组转列：按行号分组，使用CASE WHEN将每个生产线的Machine_Type映射到对应列，MAX()函数用于过滤NULL值，保留当前行号下对应生产线的类型值。

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