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

ghz 1years ago ⋅ 3049 views

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

Try using below query:

 WITH T AS (SELECT 'A,B,C,D,E,F' STR  FROM DUAL)   SELECT    
 REGEXP_SUBSTR (STR, '[^,]+', 1, LEVEL) SPLIT_VALUES  FROM T 
 CONNECT BY LEVEL <= (SELECT LENGTH (REPLACE (STR, ',', NULL)) FROM T)

Below Query with ID:

WITH TAB AS 
(SELECT '1001' ID, 'A,B,C,D,E,F' STR FROM DUAL
)
SELECT    ID, 
REGEXP_SUBSTR (STR, '[^,]+', 1, LEVEL) SPLIT_VALUES  FROM TAB 
CONNECT BY LEVEL <= (SELECT LENGTH (REPLACE (STR, ',', NULL)) FROM TAB);

EDIT: Try using below query for multiple IDs and multiple separation:

WITH TAB AS 
(SELECT '1001' ID, 'A,B,C,D,E,F' STR FROM DUAL
UNION
SELECT '1002' ID, 'D,E,F' STR FROM DUAL
UNION
SELECT '1003' ID, 'C,E,G' STR FROM DUAL
)
select id, substr(STR, instr(STR, ',', 1, lvl) + 1, instr(STR, ',', 1, lvl + 1) - instr(STR, ',', 1, lvl) - 1) name 
from
( select ',' || STR || ',' as STR, id from TAB ),
( select level as lvl from dual connect by level <= 100 )
where lvl <= length(STR) - length(replace(STR, ',')) - 1

order by ID, NAME
#oracle