어떻게 변환 쉼표는 오라클의 행에 값을 분리?

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

해결법

==============================

1.나는 이것이 정말 나쁜 디자인에 동의합니다. 당신이 디자인을 변경할 수없는 경우이 시도 :

나는 이것이 정말 나쁜 디자인에 동의합니다. 당신이 디자인을 변경할 수없는 경우이 시도 :

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;

산출

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;

당신은 CTE를 가진 "ANSIer"접근 방식의 이동을 원하는 경우 :

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;

30000 개 행이 데이터 집합 나는 3 개 방법을 시도하고 118,104 행이 반환 다음과 같은 평균 결과를 얻었다 :

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

==============================

2.이것은 중복을 제거 할 필요하거나 CONNECT BY의 SYS_GUID () 또는 DBMS_RANDOM.VALUE ()를 포함하는 해킹을 사용하지 않고 값을 얻을 것이다 :

이것은 중복을 제거 할 필요하거나 CONNECT BY의 SYS_GUID () 또는 DBMS_RANDOM.VALUE ()를 포함하는 해킹을 사용하지 않고 값을 얻을 것이다 :

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;

==============================
3.베르 첼리는 정답을 기록했다. 그러나 분할에 하나 이상의 문자열로, 많은, 많은 중복이있는 행의 기하 급수적으로 증가하는 번호를 생성하여 연결. (그냥 구분없이 쿼리를 시도하십시오.)이 아닌 사소한 크기의 데이터에 대한 성능을 파괴 할 것이다.

베르 첼리는 정답을 기록했다. 그러나 분할에 하나 이상의 문자열로, 많은, 많은 중복이있는 행의 기하 급수적으로 증가하는 번호를 생성하여 연결. (그냥 구분없이 쿼리를 시도하십시오.)이 아닌 사소한 크기의 데이터에 대한 성능을 파괴 할 것이다.

이 문제를 해결하는 한 가지 일반적인 방법은 이전 상태와 계층 구조에서 피하기 사이클에 대한 추가 검사를 사용하는 것입니다. 그래서 같이 :
```
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

==============================

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

또 다른 방법은 간단한 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

from https://stackoverflow.com/questions/38371989/how-to-convert-comma-separated-values-to-rows-in-oracle by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 왼쪽, 오른쪽, 외부 및 내부 조인의 차이점은 무엇입니까? (0)	2020.03.12
[SQL] 어떻게 오라클 데이터베이스와 일치하는 문자열의 큰 숫자를로드 할? (0)	2020.03.12
[SQL] 포함 된 VARCHAR 필드에 문자열의 발생 수를 계산? (0)	2020.03.12
[SQL] 집계 함수없이 TSQL 피벗 (0)	2020.03.12
[SQL] JSON 배열의 요소를 찾기위한 색인 (0)	2020.03.12

복붙노트

[SQL] 어떻게 변환 쉼표는 오라클의 행에 값을 분리?

어떻게 변환 쉼표는 오라클의 행에 값을 분리?

해결법

1.나는 이것이 정말 나쁜 디자인에 동의합니다. 당신이 디자인을 변경할 수없는 경우이 시도 :

2.이것은 중복을 제거 할 필요하거나 CONNECT BY의 SYS_GUID () 또는 DBMS_RANDOM.VALUE ()를 포함하는 해킹을 사용하지 않고 값을 얻을 것이다 :

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

'SQL' 카테고리의 다른 글

티스토리툴바