데이터베이스 질문과 답변
한 컬럼에 쉼표(,) 구분으로 되어있는 데이터 분리 및 변환 질문
1
2
792
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]
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]
와우..마농님은 정말 대단하시네요...
전 이렇게 하고 있었는데요..ㅎㅎ
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
;
) 버튼을 클릭하여 작성 하시면 됩니다.