[MSSQL] row를 합쳐 한줄로 표현하기

FOR XML (SQL Server) - SQL Server | Microsoft Learn

FOR XML (SQL Server) - SQL Server

 

FOR XML [MODE] : 쿼리실행 결과를 XML 형식으로 만들어준다.
[MODE]를 간략히 소개하면  
ROW : 조회된 row를 <row /> 태그안에 담아 반환해준다.
AUTO : 테이블 특성에 따라 자동으로 태그를 생성하여 반환해준다.
EXPLICIT : 계층별로 사용자가 지정하여 태그를 생성하여 줄수있다.
PATH : EXPLICIT모드를 간략하게 사용할 수 있다.
문자열을 합치는데에는 PATH 옵션을 사용할 것이고
자세한 설명은 ms의 공식 문서를 확인해 보도록 한다.

사용자의 취미를 저장하는 테이블을 임의 생성하고 데이터를 세팅해주었다.
DECLARE @tmpTbl TABLE ([user_name] VARCHAR(30), [hobby] VARCHAR(30))

 

INSERT @tmpTbl 
VALUES
('홍길동', '축구'),
('홍길동', '농구')

SELECT [user_name], [hobby]
FROM @tmpTbl
결과 >
user_name                      hobby
------------------------------ ------------------------------
홍길동                            축구
홍길동                            농구

실제 FOR XML을 사용하여 데이터를 확인해보자

SELECT [user_name], [hobby]
FROM @tmpTbl
FOR XML PATH('')
결과 >
XML_F52E2B61-18A1-11d1-B105-00805F49916B
-----------------------------------------------------------------------------------------
<user_name>홍길동</user_name><hobby>축구</hobby><user_name>홍길동</user_name><hobby>농구</hobby>

컬럼명은 지정해주지 않아 XML_F52E2B61-18A1-11d1-B105-00805F49916B 이렇게 나오는 것이므로 무시하고
실제 데이터를 보면 2row를 한개의 row로 합쳐서 xml형태로 반환해주는것을 확인할수있다.
FOR XML PATH('')의 괄호 안에 넣어주는 문자의 경우 각 row를 감싸줄 부모태그를 설정해 주는것으로 테스트를 해보면 아래와 같다.

SELECT [user_name], [hobby]
FROM @tmpTbl
FOR XML PATH('MyTag')
결과 >
XML_F52E2B61-18A1-11d1-B105-00805F49916B
-----------------------------------------------------------------------------------------
<MyTag><user_name>홍길동</user_name><hobby>축구</hobby></MyTag><MyTag><user_name>홍길동</user_name><hobby>농구</hobby></MyTag>

본론으로 돌아와서
우리가 최종적으로 얻고 싶은 결과물은
홍길동 | 축구,농구
이기때문에 hobby 컬럼을 합쳐줘야 한다.

SELECT [user_name],
       (SELECT [hobby] FROM @tmpTbl WHERE [user_name] =  a.[user_name] FOR XML PATH('')) AS hobby
FROM @tmpTbl a
결과 >
user_name                      hobby
------------------------------ ------------------------------------------------------------
홍길동                            <hobby>축구</hobby><hobby>농구</hobby>
홍길동                            <hobby>축구</hobby><hobby>농구</hobby>

눈치챈 사람도 있겠지만 FOR XML을 통해 만들어진 태그들을 보면 column명으로 만들어 졌다는 것을 확인할 수 있다.
우리가 원하는 축구,농구 를 만들기 위해서 컬럼명을 없애주어야 한다는 것인데, 방법은 간단하다.
컬럼에 특정 문자를 합해주기만 하면된다. 그냥 공백을 + 해줘도 되지만 우리는 축구,농구 와 같은 데이터를 만들어 줄 것이기 때문에 ,를 붙여주면 될것같다.

SELECT [user_name],
       (SELECT ',' + [hobby] FROM @tmpTbl WHERE [user_name] =  a.[user_name] FOR XML PATH('')) AS hobby
FROM @tmpTbl a
결과 >
user_name                      hobby
------------------------------ ------------------------------
홍길동                            ,축구,농구
홍길동                            ,축구,농구

이제 맨 앞의 거슬리는 1글자만 제거해주면 된다. 이를위해 STUFF를 사용할 것이다.
STUFF ( 문자열 , 시작위치 , 치환할 문자 길이 , 치환할 문자 )

SELECT [user_name],
       STUFF((SELECT ',' + [hobby] FROM @tmpTbl WHERE [user_name] =  a.[user_name] FOR XML PATH('')), 1, 1, '') AS hobby
FROM @tmpTbl a
결과 >
user_name                      hobby
------------------------------ ------------------------------
홍길동                            축구,농구
홍길동                            축구,농구

 

이렇게 했을때 같은 값이 row만큼 나왔다. 마지막으로 DISTINCT를 붙여주어 중복제거만 해주도록 하자.

user_name                      hobby
------------------------------ ------------------------------
홍길동                            축구,농구

 

우리가 원하는 결과를 얻었다!

 

앞서 row를 합치기 위해 stuff와 FOR XML을 합쳐 변거로운 과정을 거쳤다.
그러나 SQL Server 2017버전 이상에서는 STRING_AGG 라는 아주아주 편한 함수를 제공해 주기때문에
간단하게 같은 결과를 얻을수 있다.

SELECT [user_name], STRING_AGG(hobby, ',') AS hobby
FROM @tmpTbl
GROUP BY [user_name]
결과 >
user_name                      hobby
------------------------------ ------------------------------
홍길동                            축구,농구