如何用SQL动态拆分逗号分隔值为列？兼容未来值数量增加场景

Great question! The short answer is no—you don’t have to rely solely on PL/SQL to handle this scenario. There are pure SQL approaches that can adapt when the number of comma-separated values grows (even for future rows with more values), though PL/SQL is certainly a viable and flexible option. Let’s break down both solutions:

1. Pure SQL + Dynamic Execution (Minimal PL/SQL) #

Since the number of columns you need depends on the maximum number of values in any row, dynamic SQL is key to generating the right columns on the fly. You can use Oracle’s JSON_TABLE function to split the values, then dynamically build the query to pivot them into columns.

Step 1: First, Find the Maximum Number of Values #

Run this to get the highest count of comma-separated values in your table:

SELECT MAX(REGEXP_COUNT(col1, ',') + 1) AS max_total_columns
FROM test_data;

Step 2: Build a Dynamic Query to Split and Pivot #

This block uses a tiny bit of PL/SQL to assemble the query, but the core splitting logic is pure SQL:

DECLARE
  v_max_cols NUMBER;
  v_sql_stmt VARCHAR2(4000);
BEGIN
  -- Get the maximum number of columns needed
  SELECT MAX(REGEXP_COUNT(col1, ',') + 1) INTO v_max_cols FROM test_data;

  -- Build the SELECT clause with dynamic column names
  v_sql_stmt := 'SELECT ';
  FOR i IN 1..v_max_cols LOOP
    v_sql_stmt := v_sql_stmt || 'split_vals.col' || i || ',';
  END LOOP;
  v_sql_stmt := RTRIM(v_sql_stmt, ',');

  -- Add the FROM clause with JSON_TABLE to split the values
  v_sql_stmt := v_sql_stmt || q'[
    FROM test_data,
    JSON_TABLE(
      '["' || REPLACE(col1, ',', '","') || '"]',
      '$[*]' COLUMNS (
  ]';

  -- Build the columns for JSON_TABLE
  FOR i IN 1..v_max_cols LOOP
    v_sql_stmt := v_sql_stmt || 'col' || i || ' VARCHAR2(50) PATH ''$[' || (i-1) || ']'',';
  END LOOP;
  v_sql_stmt := RTRIM(v_sql_stmt, ',') || ') split_vals';

  -- Execute the dynamic query
  EXECUTE IMMEDIATE v_sql_stmt;
END;
/

This will output exactly the columns you need, with NULL values for rows that have fewer values than the maximum (like your valA,valB,valC row getting a NULL in col4).

2. PL/SQL Solution (Full Control for Complex Workflows) #

If you need more control—like saving results to a table, adding error handling, or reusing the logic—PL/SQL is a great fit. Here’s a stored procedure that creates a temporary table with dynamic columns and populates it:

CREATE OR REPLACE PROCEDURE split_csv_to_columns(p_table_name IN VARCHAR2, p_column_name IN VARCHAR2)
IS
  v_max_cols NUMBER;
  v_sql_stmt VARCHAR2(4000);
BEGIN
  -- Drop temp table if it exists
  EXECUTE IMMEDIATE 'DROP TABLE IF EXISTS temp_split_results';

  -- Get max number of values
  EXECUTE IMMEDIATE 'SELECT MAX(REGEXP_COUNT(' || p_column_name || ', '','') + 1) FROM ' || p_table_name INTO v_max_cols;

  -- Create temp table with dynamic columns
  v_sql_stmt := 'CREATE TABLE temp_split_results (';
  FOR i IN 1..v_max_cols LOOP
    v_sql_stmt := v_sql_stmt || 'col' || i || ' VARCHAR2(50),' || CHR(10);
  END LOOP;
  v_sql_stmt := RTRIM(v_sql_stmt, ',' || CHR(10)) || ')';
  EXECUTE IMMEDIATE v_sql_stmt;

  -- Populate the temp table
  v_sql_stmt := 'INSERT INTO temp_split_results SELECT ';
  FOR i IN 1..v_max_cols LOOP
    v_sql_stmt := v_sql_stmt || 'split_vals.col' || i || ',';
  END LOOP;
  v_sql_stmt := RTRIM(v_sql_stmt, ',') || q'[
    FROM ]' || p_table_name || q'[,
    JSON_TABLE(
      '["' || REPLACE(]' || p_column_name || q'[, ',', '","') || '"]',
      '$[*]' COLUMNS (
  ]';

  FOR i IN 1..v_max_cols LOOP
    v_sql_stmt := v_sql_stmt || 'col' || i || ' VARCHAR2(50) PATH ''$[' || (i-1) || ']'',';
  END LOOP;
  v_sql_stmt := RTRIM(v_sql_stmt, ',') || ') split_vals';

  EXECUTE IMMEDIATE v_sql_stmt;
  COMMIT;

  DBMS_OUTPUT.PUT_LINE('Temporary table temp_split_results created with ' || v_max_cols || ' columns.');
END;
/

Call it like this:

EXEC split_csv_to_columns('test_data', 'col1');

Key Takeaway #

You don’t need PL/SQL for this, but dynamic SQL paired with functions like JSON_TABLE is essential because the number of columns isn’t fixed. PL/SQL just makes it easier to manage the dynamic logic, especially for reusable or complex workflows.

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