能否通过SQL实现Excel中列合并、重命名与压缩的操作？

你的需求核心是把每行的非NULL值按原列顺序左对齐，然后重命名为1到x。在MySQL中完全可以通过字符串拼接+拆分的方式实现，下面给你两种适配不同场景的方案：

方案1：静态列输出（适合固定最大列数场景） #

这种方案会返回最多25列（对应你的25个原始列），没有值的列显示为NULL，你可以在Excel中直接忽略右侧全NULL的列。步骤很清晰：

1. 拼接非NULL值：用CONCAT_WS函数按原始列顺序拼接所有非NULL值（CONCAT_WS会自动跳过NULL值），选一个不会出现在数据中的分隔符（比如|）来分隔内容。
2. 拆分字符串为列：用SUBSTRING_INDEX函数把拼接后的字符串拆分成多个列，分别命名为1、2...25。

示例SQL：

SELECT
  SUBSTRING_INDEX(SUBSTRING_INDEX(non_null_values, '|', 1), '|', -1) AS `1`,
  SUBSTRING_INDEX(SUBSTRING_INDEX(non_null_values, '|', 2), '|', -1) AS `2`,
  SUBSTRING_INDEX(SUBSTRING_INDEX(non_null_values, '|', 3), '|', -1) AS `3`,
  SUBSTRING_INDEX(SUBSTRING_INDEX(non_null_values, '|', 4), '|', -1) AS `4`,
  SUBSTRING_INDEX(SUBSTRING_INDEX(non_null_values, '|', 5), '|', -1) AS `5`,
  SUBSTRING_INDEX(SUBSTRING_INDEX(non_null_values, '|', 6), '|', -1) AS `6`,
  SUBSTRING_INDEX(SUBSTRING_INDEX(non_null_values, '|', 7), '|', -1) AS `7`,
  SUBSTRING_INDEX(SUBSTRING_INDEX(non_null_values, '|', 8), '|', -1) AS `8`,
  SUBSTRING_INDEX(SUBSTRING_INDEX(non_null_values, '|', 9), '|', -1) AS `9`,
  SUBSTRING_INDEX(SUBSTRING_INDEX(non_null_values, '|', 10), '|', -1) AS `10`,
  SUBSTRING_INDEX(SUBSTRING_INDEX(non_null_values, '|', 11), '|', -1) AS `11`,
  SUBSTRING_INDEX(SUBSTRING_INDEX(non_null_values, '|', 12), '|', -1) AS `12`,
  SUBSTRING_INDEX(SUBSTRING_INDEX(non_null_values, '|', 13), '|', -1) AS `13`,
  SUBSTRING_INDEX(SUBSTRING_INDEX(non_null_values, '|', 14), '|', -1) AS `14`,
  SUBSTRING_INDEX(SUBSTRING_INDEX(non_null_values, '|', 15), '|', -1) AS `15`,
  SUBSTRING_INDEX(SUBSTRING_INDEX(non_null_values, '|', 16), '|', -1) AS `16`,
  SUBSTRING_INDEX(SUBSTRING_INDEX(non_null_values, '|', 17), '|', -1) AS `17`,
  SUBSTRING_INDEX(SUBSTRING_INDEX(non_null_values, '|', 18), '|', -1) AS `18`,
  SUBSTRING_INDEX(SUBSTRING_INDEX(non_null_values, '|', 19), '|', -1) AS `19`,
  SUBSTRING_INDEX(SUBSTRING_INDEX(non_null_values, '|', 20), '|', -1) AS `20`,
  SUBSTRING_INDEX(SUBSTRING_INDEX(non_null_values, '|', 21), '|', -1) AS `21`,
  SUBSTRING_INDEX(SUBSTRING_INDEX(non_null_values, '|', 22), '|', -1) AS `22`,
  SUBSTRING_INDEX(SUBSTRING_INDEX(non_null_values, '|', 23), '|', -1) AS `23`,
  SUBSTRING_INDEX(SUBSTRING_INDEX(non_null_values, '|', 24), '|', -1) AS `24`,
  SUBSTRING_INDEX(SUBSTRING_INDEX(non_null_values, '|', 25), '|', -1) AS `25`
FROM (
  SELECT CONCAT_WS('|',
    operator1lot1, operator2lot1, operator3lot1, operator4lot1, operator5lot1,
    operator1lot2, operator2lot2, operator3lot2, operator4lot2, operator5lot2,
    operator1lot3, operator2lot3, operator3lot3, operator4lot3, operator5lot3,
    operator1lot4, operator2lot4, operator3lot4, operator4lot4, operator5lot4,
    operator1lot5, operator2lot5, operator3lot5, operator4lot5, operator5lot5
  ) AS non_null_values
  FROM your_table -- 替换成你的实际表名
) AS temp;

方案2：动态列输出（自动匹配最大非NULL值数量） #

如果希望SQL自动返回恰好x列（x为所有行中最多的非NULL值数量），就需要用动态SQL来实现：

1. 计算每行的非NULL值数量，找到所有行中的最大值max_cols。
2. 根据max_cols动态生成拆分列的SQL语句。

示例SQL：

-- 1. 计算所有行中最多的非NULL值数量
SET @max_cols = (
  SELECT MAX(non_null_count) FROM (
    SELECT
      (operator1lot1 IS NOT NULL) +
      (operator2lot1 IS NOT NULL) +
      (operator3lot1 IS NOT NULL) +
      (operator4lot1 IS NOT NULL) +
      (operator5lot1 IS NOT NULL) +
      (operator1lot2 IS NOT NULL) +
      (operator2lot2 IS NOT NULL) +
      (operator3lot2 IS NOT NULL) +
      (operator4lot2 IS NOT NULL) +
      (operator5lot2 IS NOT NULL) +
      (operator1lot3 IS NOT NULL) +
      (operator2lot3 IS NOT NULL) +
      (operator3lot3 IS NOT NULL) +
      (operator4lot3 IS NOT NULL) +
      (operator5lot3 IS NOT NULL) +
      (operator1lot4 IS NOT NULL) +
      (operator2lot4 IS NOT NULL) +
      (operator3lot4 IS NOT NULL) +
      (operator4lot4 IS NOT NULL) +
      (operator5lot4 IS NOT NULL) +
      (operator1lot5 IS NOT NULL) +
      (operator2lot5 IS NOT NULL) +
      (operator3lot5 IS NOT NULL) +
      (operator4lot5 IS NOT NULL) +
      (operator5lot5 IS NOT NULL) AS non_null_count
    FROM your_table -- 替换成你的实际表名
  ) AS temp
);

-- 2. 动态生成拆分列的SQL
SET @sql = '';
SET @i = 1;
WHILE @i <= @max_cols DO
  SET @sql = CONCAT(@sql, 
    'SUBSTRING_INDEX(SUBSTRING_INDEX(non_null_values, ''|'', ', @i, '), ''|'', -1) AS `', @i, '`, '
  );
  SET @i = @i + 1;
END WHILE;

-- 去掉最后多余的逗号和空格
SET @sql = LEFT(@sql, LENGTH(@sql) - 2);

-- 拼接完整的SQL语句
SET @sql = CONCAT('SELECT ', @sql, ' FROM (
  SELECT CONCAT_WS(''|'',
    operator1lot1, operator2lot1, operator3lot1, operator4lot1, operator5lot1,
    operator1lot2, operator2lot2, operator3lot2, operator4lot2, operator5lot2,
    operator1lot3, operator2lot3, operator3lot3, operator4lot3, operator5lot3,
    operator1lot4, operator2lot4, operator3lot4, operator4lot4, operator5lot4,
    operator1lot5, operator2lot5, operator3lot5, operator4lot5, operator5lot5
  ) AS non_null_values FROM your_table
) AS temp;');

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

注意事项 #

• 记得把SQL中的your_table替换成你的实际表名。
• 选择分隔符时，要确保它不会出现在你的数据中，避免拆分错误（如果数据中有|，可以换成§或#这类特殊符号）。
• 动态SQL需要你有执行权限，并且在支持MySQL变量的客户端/工具中运行（部分可视化工具可能不支持动态SQL语法）。

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