split comma separated values into columns dynamically

Question

I am trying to find a sql to split a comma separated values which i have in a column into separate columns . I found several questions like it but none of the answer was able to handle the scenario of separated values increase in a future row . Is it not possible to do so in SQL and PL/SQL is the only solution?

Example  Data
col1
val1,val2,val3,val4... 
valA,valB,valC

Expected output
col1 col2 col3 col4 .....
val1 val2 val3 val4 .....
valA valB valC null .....

Note : So if max # of comma separated values in a row at present is 200 then i can hard code 200 regexp_substr() functions in select clause but what if a new row is added with 205 comma separated values in future ? how to handle this future possible case in sql at present.

Answer 1

Don't use columns - if you need to transform this to columns then do it dynamically on whatever client you are using to read from the database and just return the result as rows in your query with an associated index to indicate which column it should be in.

There are many, many ways to split a delimited string in Oracle.

One that does not use regular expressions is:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE data ( cols ) AS
  SELECT 'col1' FROM DUAL UNION ALL
  SELECT 'val1,val2,val3,val4' FROM DUAL UNION ALL
  SELECT 'valA,valB,valC' FROM DUAL;

Query 1:

WITH bounds ( id, list, start_pos, end_pos, lvl ) AS (
  SELECT ROWNUM,
         cols,
         1,
         INSTR( cols, ',' ),
         1
  FROM   data
UNION ALL
  SELECT id,
         list,
         end_pos + 1,
         INSTR( list, ',', end_pos + 1 ),
         lvl + 1
  FROM   bounds
  WHERE  end_pos > 0
)
SELECT id,
       SUBSTR(
         list,
         start_pos,
         DECODE( end_pos, 0, LENGTH( list ) + 1, end_pos ) - start_pos
       ) AS item,
       lvl,
       MAX( lvl ) OVER () AS num_columns
FROM   bounds
ORDER BY id, lvl

Results:

| ID | ITEM | LVL | NUM_COLUMNS |
|----|------|-----|-------------|
|  1 | col1 |   1 |           4 |
|  2 | val1 |   1 |           4 |
|  2 | val2 |   2 |           4 |
|  2 | val3 |   3 |           4 |
|  2 | val4 |   4 |           4 |
|  3 | valA |   1 |           4 |
|  3 | valB |   2 |           4 |
|  3 | valC |   3 |           4 |

Query 2:

If you want to pivot the output to rows in pure SQL then you will need to know the maximum number of columns and, if you do, then you can just use a PIVOT (which is why, since you do not appear to have a fixed maximum, I say to output it in rows and transform it in the client):

WITH bounds ( id, list, start_pos, end_pos, lvl ) AS (
  SELECT ROWNUM,
         cols,
         1,
         INSTR( cols, ',' ),
         1
  FROM   data
UNION ALL
  SELECT id,
         list,
         end_pos + 1,
         INSTR( list, ',', end_pos + 1 ),
         lvl + 1
  FROM   bounds
  WHERE  end_pos > 0
),
items ( id, item, col ) AS (
  SELECT id,
         SUBSTR(
           list,
           start_pos,
           DECODE( end_pos, 0, LENGTH( list ) + 1, end_pos ) - start_pos
         ),
         lvl
  FROM   bounds
)
SELECT *
FROM   items
PIVOT  (
  MAX( item ) FOR col IN (
    1 AS col1,
    2 AS col2,
    3 AS col3,
    4 AS col4
  )
)
ORDER BY id

Results:

| ID | COL1 |   COL2 |   COL3 |   COL4 |
|----|------|--------|--------|--------|
|  1 | col1 | (null) | (null) | (null) |
|  2 | val1 |   val2 |   val3 |   val4 |
|  3 | valA |   valB |   valC | (null) |

If the maximum number of columns changes then you can easily update the query by changing the PIVOT section at the end and hard-coding the new maximum number of column.

If you do not know the maximum and must do it in the database then you will need to use PL/SQL to generate a dynamic query but this is not likely to be performant.