[목표 ]
Oracle, Mssql에서 다중 행 데이터를 한 행에 나열해 출력
MSSQL 먼저 설명, ORACLE만 보고 싶다면 아래로 스크롤..
다중 행의 데이터를 쉼표(,) 와 같은 구분자를 사용해서 한 행에 출력하고 싶을때가 있다.
| FILE_ID | |
| 1 | file1, file2, file3, file4, file5 |
TYPE 같이 특정 컬럼 기준으로 그룹핑 할 수도 있다.
| TYPE | FILE_ID | |
| 1 | A | file1, file2 |
| 2 | B | file3 |
| 3 | C | file4, file5 |
실제 실무에서 사용하게 됐던 경우는
1개의 게시글에 멀티 file이 저장되어 있을 때 게시글 별 첨부파일 아이디들을 불러와야 했을때다.
이와 같은 경우 JAVA에서 이어붙여도 되긴 하지만 쿼리로 한번에 해결이 가능하다.
: 각 행(raw)으로 구성된 file_id 를 한 행에 구분기호로 나열하여 가져오기
* 완성 예시
| BOARD_NO | FILE_ID | |
| 1 | 100 | file1, file2, file3, file4, file5 |
MSSQL 에서의 문자열 합치기 설명 목차는 아래와 같다.
1. STUFF 구문의 이해
2. FOR XML 구문의 이해
3. 완성 구문
4. 상세 설명
5. GROUP BY 사용하여 그룹별 출력 (실무에서 유용)
[MS SQL] 에서 문자열 합치기 :
사용 함수 : STUFF, FOR XML PATH
DB에서 여러행의 문자열을 합치기 위해 Oracle 에 LISTAGG가 있다면 MSSQL에는 FOR XML PATH + STUFF() 조합이 있다.
1. 완성 구문
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(''):
- 행들을 연결하여 하나의 긴 문자열 생성 (', Alice, Bob, Charlie')
- .value(...):
- XML을 일반 문자열로 변환
- STUFF
- 맨 앞에 붙은 불필요한 , 제거
- 결과: 'Alice, Bob, Charlie'
STUFF 함수: 문자열의 일부를 [원하는 문자]로 치환하는 함수.
=> 주어진 문자열에서 특정 위치부터 지정한 길이만큼 문자를 삭제하고, 그 자리에 다른 문자열을 삽입.
FOR XML : 쿼리의 실행 결과를 XML형식으로 만들어줌.
2. STUFF 구문
SELECT STUFF (문자열 ,시작위치, 삭제할길이,'치환문자')
FROM 테이블명
2-1. STUFF 예시
-- 고객 이름이 '홍길동'인 경우, 2번째부터 1개의 문자를 '*'로 대체
SELECT STUFF(customer_name, 2, 1, '*') AS modified_name
FROM Customers; -- 결과 : 홍*동
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 태그 없이 순수한 문자열을 얻을 수 있다.
결과 : 김영주김소진김만
[상세 설명] |
1. 문자열 작성
문자열 부분에는 서브쿼리가 올 수 있습니다.
강제로 '홍길동' 과 같은 문자를 고정해서 사용하는 경우는 실무에서 거의 없다. 데이터베이스에서 해당하는 문자열을 SELECT 해서 가져온다.
2. FOR XML PATH('') 로 여러행의 데이터를 하나의 문자열 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 과 같은 문자열을 얻어내는 개념이다.
(요 구문 덕분에 맨 앞 쉼표 삭제됨!)
눈에 보이는 예제를 원한다면 치환문자 부분에 @, # 등 특수문자를 이용해 테스트 해보면 좋다.
[예제 -특수문자 사용]
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자리 구분자만 '##' 으로 치환된것을 볼 수 있다.
* 주의사항
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를 가져올 수 있다. ( 그룹핑 하고 싶은 데이터로 GROUP BY 해주면 됩니다.)
[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 -- Tip 1. '+' 연산자로 문자 ',' 와 FILE_ID 컬럼을 합쳐주는 작업
FROM BOARD -- SELECT 구문은 자유롭게 작성.
WHERE CODE = a.CODE
FOR XML PATH('')
), 1, 1, '') AS FILE_ID_LIST -- Tip2. 1번째 위치의 1자리 구분자길이를 '' 으로 치환한다는 의미의 STUFF()
FROM BOARD AS a
WHERE a.USE_YN ='Y'
-- CODE 컬럼을 기준으로 GROUP BY
GROUP BY a.CODE
[결과] CODE 컬럼별 FILE_ID
| CODE | FILE_ID |
| CODE_A | 1,2,3,4 |
| CODE_B | 5,6 |
| CODE_C | 7 |
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 사용 찾아볼 것)
LISTAGG는 집계 함수(Aggregate Function)로,
SUM, AVG, MAX처럼 그룹 내 여러 행을 하나의 결과로 합쳐주는 함수입니다. 다만 숫자 계산이 아니라 문자열을 이어주는 역할을 합니다.
, & @ 등의 구분자를 통해 가져오는 방법으로 LISTAGG를 사용한다. ex) 카테고리 별 메뉴 목록
| CATEGORY | CONTENT |
| FOOD | 치킨&떡볶이&곱창 |
[목적]
아래 표와 같이 CODE별로 FILE_ID를 한 행에 합치는 구문을 작성
[구문]
SELECT LISTAGG(컬럼명, '구분자') WITHIN GROUP (ORDER BY 정렬기준컬럼 [ASC|DESC] )
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를 통해 문자열을 합쳐 한건으로 만들어진 것이다. 따라서 CODE 컬럼은 그룹핑을 통해 중복을 제거 해야지만 데이터가 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 별로 데이터를 한 행에 출력하고 싶을 때
LISTAGG(MS 에서는 STUFF)를 사용하여 구분자로 연결 후 한 행 으로 합칠 수 있다.
적용 후
| 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 |
