데이터베이스 질문과 답변

한 컬럼에 쉼표(,) 구분으로 되어있는 데이터 분리 및 변환 질문 1 2 1,151

by 힘내라김대리 [SQL Query] mssql query 쉼표 데이터분리 [2021.03.30 10:36:38]

안녕하세요. 궁금한게 있어서 질문 드립니다.

 

하나의 컬럼에 붉규칙적으로 데이터가 적재되어있는데, 이 데이터들을 변환 해야하는 방법에 대해서 질문을 드립니다.

 

id value
1 A,B,C,D,E,F
2 A,B,C
3 A,D,E,F

 

위에 표와 같이 데이터가 적재되어있습니다.

각각의 문자를 숫자로 변환하려고 합니다.

A -> 1

B -> 2

C -> 3

D -> 4

E - > 5

F - >6

 

변환하였을때의 값은 아래와 같이 표시되야합니다.

id value
1 1,2,3,4,5,6
2 1,2,3
3 1,4,5,6

 

이렇게 변환하는것이,,,SQL에서 가능한 부분일까요??

by 마농 [2021.03.30 11:26:32]
?
1
2
3
4
5
6
7
8
9
10
11
WITH t AS
(
SELECT 1 id, 'A,B,C,D,E,F' v
UNION ALL SELECT 2, 'A,B,C'
UNION ALL SELECT 3, 'A,D,E,F'
)
SELECT id
     , v
     , TRANSLATE(v, 'ABCDEF', '123456') x
  FROM t
;

 

by 동동동 [2021.03.30 11:51:04]

와우..마농님은 정말 대단하시네요...

전 이렇게 하고 있었는데요..ㅎㅎ

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
WITH TMP AS (
SELECT '1' ID, 'A,B,C,D,E,F' VAL FROM DUAL UNION ALL
SELECT '2' ID, 'A,B,C'       VAL FROM DUAL UNION ALL
SELECT '3' ID, 'A,D,E,F'     VAL FROM DUAL
)
SELECT ID, RTRIM(XMLAGG(XMLELEMENT(x,CVT_DATE,',')).EXTRACT('//text()'),',') VAL
  FROM (
SELECT ID
     , SUBSTR(REPLACE(VAL, ','), LV, 1) DATA
     , ASCII(SUBSTR(REPLACE(VAL, ','), LV, 1)) - 64 CVT_DATE
 FROM TMP A
    , (SELECT LEVEL LV
         FROM DUAL
      CONNECT BY LEVEL <= 1000) B
 WHERE B.LV <= LENGTH (A.VAL) - LENGTH (REPLACE (A.VAL, ',')) + 1
ORDER BY ID, LV
)
GROUP BY ID
;
 
 
WITH TMP AS (
SELECT '1' ID, 'A,B,C,D,E,F' VAL FROM DUAL UNION ALL
SELECT '2' ID, 'A,B,C'       VAL FROM DUAL UNION ALL
SELECT '3' ID, 'A,D,E,F'     VAL FROM DUAL
)
SELECT ID, Substr(MAX(SYS_CONNECT_BY_PATH(CVT_DATA, ',')), 2) VAL
  FROM (
SELECT ID
     , SUBSTR(REPLACE(VAL, ','), LV, 1) DATA
     , ASCII(SUBSTR(REPLACE(VAL, ','), LV, 1)) - 64 CVT_DATA
     , row_number() over(partition by ID Order By 1) RN
 FROM TMP A
    , (SELECT LEVEL LV
         FROM DUAL
      CONNECT BY LEVEL <= 1000) B
 WHERE B.LV <= LENGTH (A.VAL) - LENGTH (REPLACE (A.VAL, ',')) + 1
ORDER BY ID, LV
)
START WITH RN = 1     
CONNECT BY PRIOR RN = RN - 1
       AND PRIOR ID = ID
Group By ID
;

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입