通过Oracle SQL查询将逗号分隔的行分隔开(Split comma separated values of a column in row, through Oracle SQL query)

我有一个如下表格：

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

我希望这些值显示为：

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

我试着做：

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

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

最满意答案

尝试使用以下查询：

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)

以下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);

编辑：尝试使用下面的查询多个ID和多个分隔：

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

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 SQL查询将逗号分隔的行分隔开(Split comma separated values of a column in row, through Oracle SQL query)

我有一个如下表格：

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

我希望这些值显示为：

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

我试着做：

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

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

最满意答案

尝试使用以下查询：

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)

以下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);

编辑：尝试使用下面的查询多个ID和多个分隔：

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

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