如何将逗号分隔值转换为oracle中的行？

Question

这是 DDL——

 create table tbl1 (
   id number,
   value varchar2(50)
);

insert into tbl1 values (1, 'AA, UT, BT, SK, SX');
insert into tbl1 values (2, 'AA, UT, SX');
insert into tbl1 values (3, 'UT, SK, SX, ZF');

注意，这里的值是 逗号分隔 的字符串。

但是，我们需要如下结果-

 ID VALUE
-------------
1  AA
1  UT
1  BT
1  SK
1  SX
2  AA
2  UT
2  SX
3  UT
3  SK
3  SX
3  ZF

我们如何为此编写 SQL？

原文由 Samiul Al Hossaini 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

SELECT  COL1,   COL2
FROM    (   SELECT INDX, MY_STR1, MY_STR2, COL1_ELEMENTS, COL1, COL2_ELEMENTS, COL2
            FROM    (   SELECT 0 "INDX", COL1 "MY_STR1", COL1_ELEMENTS, COL1, '' "MY_STR2", COL2_ELEMENTS, COL2
                        FROM(
                                SELECT
                                    REPLACE(COL1, ', ', ',') "COL1",    -- In case there is a space after comma
                                    Trim(Length(Replace(COL1, ' ', ''))) - Trim(Length(Translate(REPLACE(COL1, ', ', ','), 'A,', 'A'))) + 1 "COL1_ELEMENTS",    -- Number of elements
                                    Replace(COL2, ', ', ',') "COL2",    -- In case there is a space after comma
                                    Trim(Length(Replace(COL2, ' ', ''))) - Trim(Length(Translate(REPLACE(COL2, ', ', ','), 'A,', 'A'))) + 1 "COL2_ELEMENTS"     -- Number of elements
                                FROM
                                    (SELECT 'aaa,bbb,ccc' "COL1", 'qq, ww, ee' "COL2" FROM DUAL)        -- Your example data
                            )
                    )
                MODEL       -- Modeling --> INDX = 0    COL1='aaa,bbb,ccc'      COL2='qq,ww,ee'
                    DIMENSION BY(0 as INDX)
                    MEASURES(COL1, COL1_ELEMENTS, COL2, CAST('a' as VarChar2(4000)) as MY_STR1, CAST('a' as VarChar2(4000)) as MY_STR2)
                    RULES ITERATE (10)      --UNTIL (ITERATION_NUMBER <= COL1_ELEMENTS[ITERATION_NUMBER + 1]) -- If you don't know the number of elements this should be bigger then you aproximation. Othewrwise it will split given number of elements
                    (
                        COL1_ELEMENTS[ITERATION_NUMBER + 1] = COL1_ELEMENTS[0],
                        MY_STR1[0] = COL1[CV()],
                        MY_STR1[ITERATION_NUMBER + 1] = SubStr(MY_STR1[ITERATION_NUMBER], InStr(MY_STR1[ITERATION_NUMBER], ',', 1) + 1),
                        COL1[ITERATION_NUMBER + 1] = SubStr(MY_STR1[ITERATION_NUMBER], 1, CASE WHEN InStr(MY_STR1[ITERATION_NUMBER], ',') <> 0 THEN InStr(MY_STR1[ITERATION_NUMBER], ',')-1 ELSE Length(MY_STR1[ITERATION_NUMBER]) END),
                        MY_STR2[0] = COL2[CV()],
                        MY_STR2[ITERATION_NUMBER + 1] = SubStr(MY_STR2[ITERATION_NUMBER], InStr(MY_STR2[ITERATION_NUMBER], ',', 1) + 1),
                        COL2[ITERATION_NUMBER + 1] = SubStr(MY_STR2[ITERATION_NUMBER], 1, CASE WHEN InStr(MY_STR2[ITERATION_NUMBER], ',') <> 0 THEN InStr(MY_STR2[ITERATION_NUMBER], ',')-1 ELSE Length(MY_STR2[ITERATION_NUMBER]) END)
                    )
        )
WHERE INDX > 0 And INDX <= COL1_ELEMENTS    -- INDX 0 contains starting strings
--
--  COL1  COL2
--  ----  ----
--  aaa   qq
--  bbb   ww
--  ccc   ee

原文由 d r 发布，翻译遵循 CC BY-SA 4.0 许可协议