Separate comma-separated values ​​into columns dynamically

Question

I am trying to find sql to separate comma separated values ​​that I have in a column into separate columns. I found several such questions, but none of the answers could handle the split value script on the next line. Is it not possible to do this 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 the max # of comma-separated values ​​in a row is currently 200, then I can hard code the 200 regexp_substr () functions in the select clause, but what if a new row is added with 205 commas in the future? how to handle this future possible case in sql at present.

Answer 1

- , , , , , .

Oracle.

, :

SQL Fiddle

Oracle 11g R2:

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;

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

:

| 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 |

2:

SQL, , , PIVOT ( , , , , ):

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

:

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

, , PIVOT .

, PL/SQL , .