如何动态创建SQL列：根据字符串拆分数量自动生成对应列

问题场景 #

现有如下数据：

ID      Value
A       123, 456, 789
B       234, 567

需要将Value列的逗号分隔值拆分为动态数量的列，输出如下（列数随最大拆分数量自动调整）：

ID  Value1 Value2  Value3
A    123    456     789
B    234    567     NULL 

常规的regexp_substr写法需要提前知晓最大拆分数量，无法自适应数据变化，以下是不同数据库下的动态实现方案：

通用思路：动态SQL #

核心逻辑是先计算Value列的最大拆分数量，再通过循环拼接包含对应拆分逻辑的SQL语句，最后执行动态生成的查询。

Oracle 实现 #

1. 先计算最大拆分列数：

SELECT MAX(REGEXP_COUNT(Value, '[^,]+')) AS max_cols FROM your_table;

2. 使用PL/SQL生成并执行动态查询：

DECLARE
  v_max_cols NUMBER;
  v_sql VARCHAR2(4000);
BEGIN
  SELECT MAX(REGEXP_COUNT(Value, '[^,]+')) INTO v_max_cols FROM your_table;
  
  v_sql := 'SELECT ID';
  FOR i IN 1..v_max_cols LOOP
    v_sql := v_sql || ', REGEXP_SUBSTR(Value, ''[^,]+'', 1, ' || i || ') AS Value' || i;
  END LOOP;
  v_sql := v_sql || ' FROM your_table';
  
  EXECUTE IMMEDIATE v_sql;
END;
/

MySQL 实现 #

1. 计算最大拆分列数：

SELECT MAX(LENGTH(Value) - LENGTH(REPLACE(Value, ',', '')) + 1) AS max_cols FROM your_table;

2. 创建并调用存储过程实现动态查询：

DELIMITER //
CREATE PROCEDURE dynamic_split()
BEGIN
  DECLARE max_cols INT;
  DECLARE i INT DEFAULT 1;
  DECLARE sql_str VARCHAR(1000) DEFAULT 'SELECT ID';
  
  SELECT MAX(LENGTH(Value) - LENGTH(REPLACE(Value, ',', '')) + 1) INTO max_cols FROM your_table;
  
  WHILE i <= max_cols DO
    SET sql_str = CONCAT(sql_str, ', REGEXP_SUBSTR(Value, ''[^,]+'', 1, ', i, ') AS Value', i);
    SET i = i + 1;
  END WHILE;
  
  SET sql_str = CONCAT(sql_str, ' FROM your_table');
  PREPARE stmt FROM sql_str;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

-- 执行存储过程
CALL dynamic_split();

PostgreSQL 实现 #

1. 计算最大拆分列数：

SELECT MAX(array_length(string_to_array(Value, ', '), 1)) AS max_cols FROM your_table;

2. 使用DO块生成并执行动态查询：

DO $$
DECLARE
  max_cols INT;
  sql_str TEXT := 'SELECT ID';
BEGIN
  SELECT MAX(array_length(string_to_array(Value, ', '), 1)) INTO max_cols FROM your_table;
  
  FOR i IN 1..max_cols LOOP
    sql_str := sql_str || format(', split_part(Value, '', '', %s) AS Value%s', i, i);
  END LOOP;
  
  sql_str := sql_str || ' FROM your_table';
  EXECUTE sql_str;
END $$;

说明 #

以上方案会自动根据当前数据中Value列的最大拆分数量生成对应列，后续数据新增拆分项时，重新执行对应代码即可生成新的列，无需手动修改SQL语句。

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