Split comma separated values of a column in row, through Oracle SQL query

Question

I have a table like below:

-------------
ID   | NAME
-------------
1001 | A,B,C
1002 | D,E,F
1003 | C,E,G
-------------

I want these values to be displayed as:

-------------
ID   | NAME
-------------
1001 | A
1001 | B
1001 | C
1002 | D
1002 | E
1002 | F
1003 | C
1003 | E
1003 | G
-------------

I tried doing:

select split('A,B,C,D,E,F', ',') from dual; -- WILL RETURN COLLECTION

select column_value
from table (select split('A,B,C,D,E,F', ',') from dual); -- RETURN COLUMN_VALUE

Answer 1

There are multiple options. See Split comma delimited strings in a table in Oracle.

Using REGEXP_SUBSTR:

SQL> WITH sample_data AS(
  2  SELECT 10001 ID, 'A,B,C' str FROM dual UNION ALL
  3  SELECT 10002 ID, 'D,E,F' str FROM dual UNION ALL
  4  SELECT 10003 ID, 'C,E,G' str FROM dual
  5  )
  6  -- end of sample_data mimicking real table
  7  SELECT distinct id, trim(regexp_substr(str, '[^,]+', 1, LEVEL)) str
  8  FROM sample_data
  9  CONNECT BY LEVEL <= regexp_count(str, ',')+1
 10  ORDER BY ID
 11  /

        ID STR
---------- -----
     10001 A
     10001 B
     10001 C
     10002 D
     10002 E
     10002 F
     10003 C
     10003 E
     10003 G

9 rows selected.

SQL>

Using XMLTABLE:

SQL> WITH sample_data AS(
  2  SELECT 10001 ID, 'A,B,C' str FROM dual UNION ALL
  3  SELECT 10002 ID, 'D,E,F' str FROM dual UNION ALL
  4  SELECT 10003 ID, 'C,E,G' str FROM dual
  5  )
  6  -- end of sample_data mimicking real table
  7  SELECT id,
  8        trim(COLUMN_VALUE) str
  9  FROM sample_data,
 10       xmltable(('"'
 11          || REPLACE(str, ',', '","')
 12          || '"'))
 13  /

        ID STR
---------- ---
     10001 A
     10001 B
     10001 C
     10002 D
     10002 E
     10002 F
     10003 C
     10003 E
     10003 G

9 rows selected.