[목표 ]
Oracle, Mssql에서 다중 행 데이터를 한 행에 나열해 출력
MSSQL 먼저 설명, ORACLE만 보고 싶다면 아래로 스크롤..
다중 행의 데이터를 쉼표(,) 와 같은 구분자를 사용해서 한 행에 출력하고 싶을때가 있다.
| FILE_ID | |
| 1 | file1, file2, file3, file4, file5 |
TYPE과 같은 컬럼 기준, GROUP별로 합치는 경우도 있다.
| TYPE | FILE_ID | |
| 1 | A | file1, file2 |
| 2 | B | file3 |
| 3 | C | file4, file5 |
실제 실무에서 사용하게 됐던 경우는,
1개의 게시글에 멀티 file이 저장되어 있을 때 게시글 별 첨부파일 아이디들을 불러와야 했을때다.(Group By)
(문제는 각 하나의 행으로 구성된 file_id 들을 한 행에 쉼표 등 구분기호로 나열한 형태로 가져와야 했었다.)
| BOARD_NO | FILE_ID | |
| 1 | 100 | file1, file2, file3, file4, file5 |
그때 사용하는 함수와 구문을 적어보았다. MSSQL 에서의 문자열 합치기 설명 목차는 아래와 같다.
1. STUFF구문
2. FOR XML 구문
3. 완성구문
4. 상세 설명
5. GROUP BY 사용하여 그룹별 출력 (실무에서 유용)
1. [MS SQL]에서 문자열 합치기 :
사용 함수 : STUFF, FOR XML
STUFF 함수: 문자열의 일부를 [원하는 문자]로 치환하는 함수.
=> 주어진 문자열에서 특정 위치부터 지정한 길이만큼 문자를 삭제하고, 그 자리에 다른 문자열을 삽입.
FOR XML : 쿼리의 실행 결과를 XML형식으로 만들어줌.
STUFF 구문
SELECT STUFF (문자열 ,시작위치, 구분자길이,'치환문자')
FROM 테이블명
STUFF 예시
-- 고객 이름이 '홍길동'인 경우, 2번째부터 1개의 문자를 '*'로 대체
SELECT STUFF(customer_name, 2, 1, '*') AS modified_name
FROM Customers; -- 결과 : 홍*동
* 예를 들어 ",1,2,3"에 STUFF를 사용하면 결과가 1,2,3이 된다.
FOR XML 구문
SELECT
(SELECT name
FROM TB_MEMBER
WHERE name LIKE '김%'
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'
) AS name* FOR XML PATH('') 구문을 사용할 때, 별도의 옵션 없이 사용하면 컬럼 이름이 XML 태그로 출력된다.
결과 : <name>김영주</name><name>김소진</name><name>김만</name>
* 태그 없이 값을 결합하려면 FOR XML PATH('')와 TYPE 및 .value()를 조합해야 XML 태그 없이 순수한 문자열을 얻을 수 있다.
결과 : 김영주김소진김만
완성 구문
SELECT DISTINCT
STUFF (( SELECT ',' + FILE_ID
-- '+' 연산자를 사용하여 문자 ',' 와 FILE_ID 컬럼을 합쳐주는 작업
FROM TB_BOARD
FOR XML PATH('')
), 1, 1, '') AS FILE_ID
FROM TB_BOARD결과 : 1,2,3,4,5
[상세 설명] |
[순서] 문자열 작성 - FOR XML PATH('') - > STUFF 구문으로 첫 번째 콤마 제거
1. 문자열 작성
문자열 부분에는 SELECT절을 사용한다.
강제로 '홍길동' 과 같은 문자를 고정해서 사용하는 경우는 실무에서 거의 없다. 데이터베이스에서 해당하는 문자열을 SELECT 해서 가져온다.
2. FOR XML PATH('') 로 여러행의 데이터를 하나의 문자열 XML 형태로 만들어준다.
FOR XML PATH('') 을 통해 쿼리의 실행 결과를 바꿔주지 않으면 오류발생
SELECT 쿼리는 결과를 행 집합으로 반환합니다. 선택적으로 조회에 FOR XML 절을 지정하여 SQL 조회의 형식 결과를 XML로 조회할수 있습니다. FOR XML 절은 최상위 쿼리 및 하위 쿼리에서 사용할 수 있습니다. 최상위 FOR XML 절은 SELECT 문에서만 사용할 수 있습니다.
예시구문대로 작성시, 데이터 형태는 ,1,2,3,4,5,6 이렇게 만들어진다.
',' 와 + 연산자를 사용했기 때문에 맨 앞에 , 가 붙는것을 기억해두자.
3. STUFF 구문 - 원 하는 위치의 문자열 치환 : 콤마 제거
1, 2번 과정에서 문자열을 한 행으로 만들었고, 문자열의 맨 앞에는 ','가 들어가 있는 상태이며,
맨 앞의 쉼표를 제거하기 위해 STUFF 구문을 사용한다.
치환 문자 부분에 '' (공백)을 입력해 문자열의 첫번째 문자 ',' 를 빈문자로 치환 한다.
즉 ,1,2,3,4,5,6 문자열의 첫번째 문자를 ''로 치환하여 1,2,3,4,5,6 과 같은 문자열을 얻어내는 개념이다.
(요 구문 덕분에 맨 앞 쉼표 삭제됨!)
쉼표때문에 헷갈린다.. 눈에 보이는 예제를 원한다~ 싶으면 치환문자 부분에 @, # 등 특수문자를 이용해 테스트 해보면 좋다.
| STUFF 구문에 적어야 하는 값 시작위치 : 1 -- 첫번째 문자를 치환해줄거니까 시작위치는 1 구분자 길이 : 1 -- 치환할 구분자는 , 이기 때문에 길이는 1 치환문자 : '' -- 빈칸인 '' 으로 치환할 겁니다. |
* 주의사항
STUFF 구문에서 CASE WHEN 절을 사용하는 경우, Alias를 지정하지 않는다.
, STUFF(( SELECT ',' + CASE WHEN METHOD_TYPE ='Z'
THEN STD_CODE_NAME + '(' + ETC_METHOD + ')'
ELSE STD_CODE_NAME END // *Alias 생략해야 함 (as문)
FROM .....
WHERE .....
FOR XML PATH('')
), 1, 1, '') AS STD_CODE_NAME
> ★ GROUP별 결과 리스트 조회
GROUP BY : CODE 별로 FILE_ID List를 가져올 수 있다.
[raw 데이터]
| CODE | FILE_ID |
| CODE_A | 1 |
| CODE_A | 2 |
| CODE_A | 3 |
| CODE_A | 4 |
| CODE_B | 5 |
| CODE_B | 6 |
| CODE_C | 7 |
[구문]
SELECT a.CODE
, STUFF (( SELECT ',' + FILE_ID -- Tip1. '+' 연산자를 사용하여 문자 ',' 와 FILE_ID 컬럼을 합쳐주는 작업
FROM BOARD -- SELECT 구문은 자유롭게 작성.
WHERE CODE = a.CODE
FOR XML PATH('')
), 1, 1, '') AS FILE_ID_LIST -- Tip2. 1번째 위치의 1자리 구분자길이를 '' 으로 치환하겠다는 의미
FROM BOARD AS a
WHERE a.USE_YN ='Y'
-- CODE 컬럼을 기준으로 GROUP BY
GROUP BY a.CODE
[결과]
| CODE | FILE_ID |
| CODE_A | 1,2,3,4 |
| CODE_B | 5,6 |
| CODE_C | 7 |
[예제 -특수문자 사용]
(CODE 별로 GROUP BY해서 FILE_ID를 가져옴)
SELECT CODE, STUFF(( SELECT '@@' + FILE_ID -- 변화를 알아보기 쉽게 @@로 작성해본 구문
FROM BOARD_FILE
WHERE BOARD_FILE IS NOT NULL -- SELECT 구문은 자유롭게 작성.
FOR XML PATH('')
), 1, 2, '##' ) AS FILE_ID -- 1번째 위치의 2자리 구분자길이를 '##' 으로 치환하겠다는 의미
FROM BOARD_FILE
GROUP BY CODE[결과]
| CODE | FILE_ID |
| CODE_A | ##1@@2@@3@@4 |
| CODE_B | ##5@@,6 |
| CODE_C | ##7 |
맨 앞 문자만 치환해주는 개념이라고 설명했다. 맨 앞 '@@' 2자리 구분자만 '##' 으로 치환된것을 볼 수 있다.
1-1. 문자열과 구분자가 다른 데이터 타입일 때
: NUMERIC 형태의 데이터와 VARCHAR 형태의 데이터 사용시
구문 사용중 아래와 같은 오류가 발생한다면?
>[에러] 스크립트 라인: 7-18 ---------------------------------
데이터 형식 varchar을(를) numeric(으)로 변환하는 중 오류가 발생했습니다.
메시지: 8114, 레벨: 16, 상태: 5, 프로시저: , 라인: 1
[설명] - CONVERT로 형변환을 해주지 않고 구문을 작성해서 발생하는 데이터 타입 오류.
MSSQL은 처음 사용해봐서 형변환 오류를 겪었다.
STUFF구문에서 활용하는 SELECT절의 맨 앞 + 연산자는 같은 형태의 데이터일때만 사용이 가능하다.
즉 ',' + NUMERIC형태인 FILE_ID 를 결합하고자 할 때 오류 발생
이슈 : NUMERIC형태의 [FILE_ID], VARCHAR 형태의 [FILE_PATH]의 차이
해결 : 형변환을 통해 데이터 타입을 맞춰줘야지만 + 연산자를 사용해 합칠 수 있음을 주의하자!
[오류 구문]
SELECT CODE
-- 현재 DB에 FILE_ID 컬럼은 NUMERIC 형태이기 때문에 ',' 와 + 할 때 오류발생
-- 형변환이 필요하다.
, STUFF (( SELECT ',' + FILE_ID
FROM DEV_BOARD_FILE
WHERE CURR_CODE = '201740001'
FOR XML PATH('')
), 1, 1, '') FILE_ID
FROM DEV_BOARD_FILE
WHERE CURR_CODE = '201740001'
GROUP BY CODE;
[해결 : CONVERT]
-- ORACLE 에서의 TO_CHAR
SELECT CODE
, STUFF(( SELECT ',' + CONVERT(VARCHAR, FILE_ID) -- FILE_ID 를 VARCHAR형태로 바꿔준다는 뜻.
FROM BOARD_FILE
WHERE CURR_CODE = '1234'
FOR XML PATH('')
), 1, 1, '') FILE_ID
FROM DEV_BOARD_FILE
WHERE CURR_CODE = '1234'
GROUP BY CODE;
*CONVERT 란 ? 데이터타입 변환 함수
구문
CONVERT (변환하고싶은 data_type[(길이)], 컬럼) ;
-- (AGE)컬럼을 INT에서 CHAR로 형변환 후 + 연산자로 결합
SELECT CONVERT(VARCHAR(10) , AGE)+'세'AS 나이 FROM MEMBER
--날짜(DTS)컬럼을 INT에서 DATE로 형변환
SELECT CONVERT(DATE , SUBSTRING(DTS,1,111))AS 날짜 FROM MEMBER
* CONVERT 날짜 변환표
* GETDATE()
ORACLE에서는 현재 시간 정보를 가져올때 SYSDATE를 사용한다.
MS-SQL에서는 GETDATE() 로 가져온다.
예제
SELECT CONVERT(VARCHAR(8), GETDATE(), 112)
-- 112라는 날짜변환 타입을 줌으로 결과는 YYYYMMDD 형태로 나옴 , * 위 날짜 변환표 참고
-- 20211202
1-2. 같은 VARCHAR 데이터타입 일때
SELECT CODE
, STUFF(( SELECT ',' + FILE_PATH -- ','와 FILE_PATH 둘다 같은 형태(VARCHAR)기 때문에 CONVERT 필요 없음.
FROM DEV_BOARD_FILE
WHERE CURR_CODE = '201740001'
FOR XML PATH('')
), 1, 1, '') FILE_ID
FROM DEV_BOARD_FILE
WHERE CURR_CODE = '201740001'
GROUP BY CODE;
2. [ORACLE] 에서 문자열 합치기 : LISTAGG(정렬가능),
: WM_CONCAT(정렬불가)
LISTAGG란, 아래로 나열되는 여러행의 데이터를 한 행으로 합치고 싶을때 사용하는 ORALCE 내장 함수
(Oracle 11g부터 사용가능, VARCHAR2형이기 때문에 4000넘으면 잘림
이러한 이슈로부터 자유로워지고 싶다면.. XMLAGG 사용 찾아볼 것)
ORACLE에서 여러 행(ROW)의 데이터를 하나로 합쳐서(한 행으로) 가져오고 싶을때
, & @ 등의 구분자를 통해 가져오는 방법으로 LISTAGG를 사용한다. (ex 치킨& 떡볶이& 곱창)
| CATEGORY | CONTENT |
| FOOD | 치킨&떡볶이&곱창 |
<목적>
아래 표와 같이 CODE별로 FILE_ID를 한 행에 합치는 구문을 작성해보자.
<구문>
SELECT LISTAGG(합치고싶은컬럼, 구분기호) WITHIN GROUP (ORDER BY 정렬기준컬럼)
FROM 테이블 ;
SELECT CODE
, LISTAGG( FILE_ID, ',' ) WITHIN GROUP (ORDER BY FILE_ID) AS FILE_ID
FROM BOARD_FILE
WHERE CODE = 'CODE_A'
GROUP BY CODE
★[중요!] SELECT 절에 LISTAGG 외의 컬럼을 표시하면 (여기서는 CODE 컬럼)
CODE컬럼은 합치고 싶은 데이터의 그룹핑 기준이 되며, GROUP BY 절 혹은 DISTINCT를 통해 중복을 제거해 줘야한다.
=> 한 행으로 합치고 싶은 FILE_ID 컬럼 의 경우 CODE컬럼을 기준으로 LISTAGG를 통해 문자열을 합쳐 한건으로 만들어진 것이다. 따라서 중복을 제거 해야지만 데이터가 1:1로 매칭되어 오류가 나지 않는다.
<예제>
CODE를 기준으로 그룹핑해서 FILE_ID를 한 행에 출력
적용 전
| CODE | FILE_ID |
| CODE_A | 1 |
| CODE_A | 2 |
| CODE_A | 3 |
| CODE_A | 4 |
| CODE_A | 5 |
| CODE_A | 6 |
이렇게 CODE 컬럼 값이 'CODE_A' 로 같지만, FILE_ID는 [1,2,3,4,5,6] 으로 각각 다르다.
CODE 별로 데이터를 1건 보고싶기 때문에 이 시점에 따라 데이터의 중복일 수 있다.
따라서, LISTAGG(MS 에서는 STUFF)를 사용하여 아래와 같이 구분자로 연결해서 1건으로 나오게, 한 행에 담을 것이다.
적용 후 데이터가 CODE 기준으로 한 행에 한 건으로 표시됨을 볼 수 있다.
적용 후
| CODE | FILE_ID |
| CODE_A | 1,2,3,4,5,6 |
'DBMS > MS-SQL' 카테고리의 다른 글
| [MSSQL] datetime 형식 날짜 조회 조건절 (0) | 2024.08.08 |
|---|---|
| [MSSQL] 프로시저 작성 While 예제 (0) | 2024.02.23 |
| [MSSQL] expression을(를) 데이터 형식 int(으)로 변환하는 중 산술 오버플로 오류가 발생했습니다. (0) | 2023.11.28 |
| [MSSQL] MSSQL 저장 프로시저 실행 구문(Stored procedure) (1) | 2023.11.21 |
| [MSSQL] 문자열 연결 / CONCAT (0) | 2022.01.20 |
