오라클에서 쉼표로 구분된 값을 행으로 변환하는 방법은 무엇입니까?

오라클에서 쉼표로 구분된 값을 행으로 변환하는 방법은 무엇입니까?

여기 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을 어떻게 작성합니까?

저는 이것이 정말 나쁜 디자인이라는 것에 동의합니다.설계를 변경할 수 없는 경우 다음을 수행합니다.

select distinct id, trim(regexp_substr(value,'[^,]+', 1, level) ) value, level
  from tbl1
   connect by regexp_substr(value, '[^,]+', 1, level) is not null
   order by id, level;

OUPUT

id value level
1   AA  1
1   UT  2
1   BT  3
1   SK  4
1   SX  5
2   AA  1
2   UT  2
2   SX  3
3   UT  1
3   SK  2
3   SX  3
3   ZF  4

이에 대한 크레딧

보다 우아하고 효율적인 방법으로 중복 제거(@mathguy에 대한 크레딧)

select id, trim(regexp_substr(value,'[^,]+', 1, level) ) value, level
  from tbl1
   connect by regexp_substr(value, '[^,]+', 1, level) is not null
      and PRIOR id =  id 
      and PRIOR SYS_GUID() is not null  
   order by id, level;

"ANSIER" 접근 방식을 원하는 경우 CTE를 사용하십시오.

with t (id,res,val,lev) as (
           select id, trim(regexp_substr(value,'[^,]+', 1, 1 )) res, value as val, 1 as lev
             from tbl1
            where regexp_substr(value, '[^,]+', 1, 1) is not null
            union all           
            select id, trim(regexp_substr(val,'[^,]+', 1, lev+1) ) res, val, lev+1 as lev
              from t
              where regexp_substr(val, '[^,]+', 1, lev+1) is not null
              )
select id, res,lev
  from t
order by id, lev;

산출량

id  val lev
1   AA  1
1   UT  2
1   BT  3
1   SK  4
1   SX  5
2   AA  1
2   UT  2
2   SX  3
3   UT  1
3   SK  2
3   SX  3
3   ZF  4

MT0에 의한 또 다른 재귀적 접근법이지만 정규식은 없습니다.

WITH t ( id, value, start_pos, end_pos ) AS
  ( SELECT id, value, 1, INSTR( value, ',' ) FROM tbl1
  UNION ALL
  SELECT id,
    value,
    end_pos                    + 1,
    INSTR( value, ',', end_pos + 1 )
  FROM t
  WHERE end_pos > 0
  )
SELECT id,
  SUBSTR( value, start_pos, DECODE( end_pos, 0, LENGTH( value ) + 1, end_pos ) - start_pos ) AS value
FROM t
ORDER BY id,
  start_pos;

3만 행 데이터 세트를 사용하여 3가지 접근 방식을 시도한 결과 11804개의 행이 반환되었으며 다음과 같은 평균 결과가 나왔습니다.

• 나의 재귀적 접근: 5초
• MT0 접근: 4초
• 수학자 접근: 16초
• MT0 재귀 접근 no-regex: 3.45초

@Mathguy는 또한 더 큰 데이터 세트로 테스트했습니다.

모든 경우에 재귀 쿼리(나는 일반 서브스트와 instr이 있는 쿼리만 테스트함)는 2-5배 더 낫습니다.다음은 문자열당 문자열 수 / 토큰 수와 계층형 대 재귀, 계층형 우선에 대한 CTAS 실행 시간의 조합입니다.모든 시간(초)

• 30,000 x 4:5 / 1.
• 30,000 x 10:15 / 3.
• 30,000 x 25: 56 / 37.
• 5,000 x 50: 33 / 14.
• 5,000 x 100: 160 / 81.
• 10,000 x 200: 1,924 / 772

이렇게 하면 중복을 제거하거나 다음을 포함하는 해킹을 사용할 필요 없이 값을 얻을 수 있습니다.SYS_GUID()또는DBMS_RANDOM.VALUE()에서CONNECT BY:

SELECT t.id,
       v.COLUMN_VALUE AS value
FROM   TBL1 t,
       TABLE(
         CAST(
           MULTISET(
             SELECT TRIM( REGEXP_SUBSTR( t.value, '[^,]+', 1, LEVEL ) )
             FROM   DUAL
             CONNECT BY LEVEL <= REGEXP_COUNT( t.value, '[^,]+' )
           )
           AS SYS.ODCIVARCHAR2LIST
         )
       ) v

업데이트:

목록에서 요소의 인덱스 반환:

옵션 1 - UDT 반환:

CREATE TYPE string_pair IS OBJECT( lvl INT, value VARCHAR2(4000) );
/

CREATE TYPE string_pair_table IS TABLE OF string_pair;
/

SELECT t.id,
       v.*
FROM   TBL1 t,
       TABLE(
         CAST(
           MULTISET(
             SELECT string_pair( level, TRIM( REGEXP_SUBSTR( t.value, '[^,]+', 1, LEVEL ) ) )
             FROM   DUAL
             CONNECT BY LEVEL <= REGEXP_COUNT( t.value, '[^,]+' )
           )
           AS string_pair_table
         )
       ) v;

옵션 2 - 사용ROW_NUMBER():

SELECT t.id,
       v.COLUMN_VALUE AS value,
       ROW_NUMBER() OVER ( PARTITION BY id ORDER BY ROWNUM ) AS lvl
FROM   TBL1 t,
       TABLE(
         CAST(
           MULTISET(
             SELECT TRIM( REGEXP_SUBSTR( t.value, '[^,]+', 1, LEVEL ) )
             FROM   DUAL
             CONNECT BY LEVEL <= REGEXP_COUNT( t.value, '[^,]+' )
           )
           AS SYS.ODCIVARCHAR2LIST
         )
       ) v;

Vercelli는 정답을 올렸습니다.그러나 둘 이상의 문자열을 분할할 경우connect by반복되는 행의 수가 기하급수적으로 증가합니다.(다음을 제외하고 쿼리를 시도해 보십시오.)distinct.) 이로 인해 사소한 크기가 아닌 데이터의 성능이 저하됩니다.

이 문제를 해결하는 일반적인 방법은 다음과 같습니다.prior계층 구조의 주기를 방지하기 위한 추가 검사를 수행합니다.이와 같은 경우:

select id, trim(regexp_substr(value,'[^,]+', 1, level) ) value, level
  from tbl1
   connect by regexp_substr(value, '[^,]+', 1, level) is not null
          and prior id = id
          and prior sys_guid() is not null
   order by id, level;

예를 들어 OTN에 대한 이 토론을 참조하십시오. https://community.oracle.com/thread/2526535

다른 방법은 간단한 PL/SQL 함수를 정의하는 것입니다.

CREATE OR REPLACE FUNCTION split_String(
  i_str    IN  VARCHAR2,
  i_delim  IN  VARCHAR2 DEFAULT ','
) RETURN SYS.ODCIVARCHAR2LIST DETERMINISTIC
AS
  p_result       SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST();
  p_start        NUMBER(5) := 1;
  p_end          NUMBER(5);
  c_len CONSTANT NUMBER(5) := LENGTH( i_str );
  c_ld  CONSTANT NUMBER(5) := LENGTH( i_delim );
BEGIN
  IF c_len > 0 THEN
    p_end := INSTR( i_str, i_delim, p_start );
    WHILE p_end > 0 LOOP
      p_result.EXTEND;
      p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, p_end - p_start );
      p_start := p_end + c_ld;
      p_end := INSTR( i_str, i_delim, p_start );
    END LOOP;
    IF p_start <= c_len + 1 THEN
      p_result.EXTEND;
      p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, c_len - p_start + 1 );
    END IF;
  END IF;
  RETURN p_result;
END;
/

그러면 SQL이 매우 단순해집니다.

SELECT t.id,
       v.column_value AS value
FROM   TBL1 t,
       TABLE( split_String( t.value ) ) v

--converting row of data into comma sepaerated string
SELECT
    department_id,
    LISTAGG(first_name, ',') WITHIN GROUP(
        ORDER BY
            first_name
    ) comma_separted_data
FROM
    hr.employees
GROUP BY
    department_id;

--comma-separated string into row of data

CREATE TABLE t (
    deptno          NUMBER,
    employee_name   VARCHAR2(255)
);

INSERT INTO t VALUES (
    10,
    'mohan,sam,john'
);

INSERT INTO t VALUES (
    20,
    'manideeep,ashok,uma'
);

INSERT INTO t VALUES (
    30,
    'gopal,gopi,manoj'
);

SELECT
    deptno,
    employee_name,
    regexp_count(employee_name, ',') + 1,
    regexp_substr(employee_name, '\w+', 1, 1)
FROM
    t,
    LATERAL (
        SELECT
            level l
        FROM
            dual
        CONNECT BY
            level < regexp_count(employee_name, ',') + 1
    );

DROP TABLE t;

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

언급URL : https://stackoverflow.com/questions/38371989/how-to-convert-comma-separated-values-to-rows-in-oracle