MSSQL Row 합치기 - MSSQL Row habchigi

MSSQL Tips

IDTitle

1	가
1	나
1	다
1	라
2	마
2	바
2	사
2	아
3	자

위와같은 테이블이 있습니다.
ID를 기준으로 Title의 텍스트를 comma(,)로 구분지어서 하나의 ROW로 합치고 싶을때 아래 쿼리를 사용합니다.

SELECT DISTINCT ID, STUFF(( SELECT ',' + Title FROM TB_Data b WHERE b.ID = a.ID FOR XML PATH('') ),1,1,'') AS NAME FROM TB_Data a

아래와 같은 결과가 출력됩니다.

IDTitle

1	가,나,다,라
2	마,바,사,아,자

STUFF?? FOR XML PATH??

STUFF함수

SUTFF함수는 문자열에서 특정 시작위치에서 지정된 길이만큼 문자를 바꿔줍니다.

SELECT STUFF('CASTLE',3,4,'P')

위 문자열에 대한 치환 결과로 CAP라는 값이 리턴됩니다.

FOR XML PATH

XML기능은 SELECT 문장에 FOR XML절을 추가하여 데이터베이스 엔진에서 쿼리결과를 XML형식으로 반환하도록 지정하거나 할 수 있는 기능입니다.

위 예제에서는 XML 기능의 여러 모드들 중 PATH 모드를 사용하였습니다.

PATH 모드를 추가 할 경우 SELECT되는 각 값마다 PATH()함수에 지정하는 태그로 감쌀수가 있습니다.

ex) FOR XML PATH('ID')일 경우 <ID>,가</ID><ID>,나</ID><ID>,다</ID><ID>,라</ID>.....

하지만 PATH()함수에 태그명이 공백으로(‘’)들어갈 경우 아래와 같이 값이 나옵니다.

ex) FOR XML PATH('')일 경우 ,가,나,다,라....

위 예제에서는 이렇게 떨어진 값에 STUFF함수로 맨앞에 ,를 제거하여 우리가 목적으로 하는 값을 리턴합니다.

참고

프로그램이 좋다 - 도깨비방망이
DBGuide.net

MSSQL에서 여러개의 행을 하나의 행으로 합치는 방법에 대해 확인 해보겠습니다.

목표로하는 최종 출력값은 아래와 같습니다.

[여러 행 조회 쿼리]

[한행으로 합치기]

*시간이 없으시다면 아래 코드를 복사해 SSMS에서 실행하면 바로 결과를 확인 할 수 있습니다.

CREATE TABLE #TEMP_TABLE( idx INT INDEX ix1_movieidx NONCLUSTERED, team_name varchar(100), player varchar(100) ) insert into #TEMP_TABLE values(1,'토트넘','손흥민') insert into #TEMP_TABLE values(2,'토트넘','델리알리') insert into #TEMP_TABLE values(3,'토트넘','헤리케인') insert into #TEMP_TABLE values(4,'토트넘','에릭센') SELECT DISTINCT team_name, STUFF(( SELECT ','+player FROM #TEMP_TABLE FOR XML PATH('') ),1,1,'') as playerList FROM #TEMP_TABLE

STUFF, FOR XML PATH 샘플 상세 설명

먼저, 임시테이블을 생성합니다.

CREATE TABLE #TEMP_TABLE( idx INT INDEX ix1_movieidx NONCLUSTERED, team_name varchar(100), player varchar(100) ) insert into #TEMP_TABLE values(1,'토트넘','손흥민') insert into #TEMP_TABLE values(2,'토트넘','델리알리') insert into #TEMP_TABLE values(3,'토트넘','헤리케인') insert into #TEMP_TABLE values(4,'토트넘','에릭센') SELECT * FROM #TEMP_TABLE

SELECT를 하면 결과는 아래와 같습니다.

아래 데이터 중 PLAYER 칼럼의 값들을 하나의 행으로 묶어 보겠습니다.

STUFF 와 FOR XML PATH를 활용해야합니다. 각각의 개념을 확인 해보겠습니다.

FOR XML PATH

FOR XML PATH는 XML형태의 값을 하나의 행에 리턴해줍니다.

아래 쿼리를 실행하면,

SELECT ','+player FROM #TEMP_TABLE FOR XML PATH('SAMPLE')

아래와 같이 XML형태의 값으로 한 행에 리턴해주게 되는데요. 

PATH() 의 파라미터 값을 빈값으로 지정해주게 되면,

아래와 같이 XML태그가 사라진 결과를 얻을 수 있습니다.

PATH()를 빈값으로 지정해주고 원하는 구분자로 이어주기만 하면 되는 것을 알 수 있습니다.

STUFF

다음으로, STUFF는 지정한 위치의 스트링값을 다른 값으로 바꿔주는 역할을 합니다.

아래 쿼리를 실행하면,

SELECT STUFF('토트넘핫스퍼',1,2,'12')

'토트넘핫스퍼' STRING 값의 '1'번 위치부터 2개 문자를  '12'로 바꿔 주게 됩니다.

결론적으로, STUFF는 위 FOR XML PATH에서 제일 앞에 붙는 구분자 값을 삭제하는데 활용하게 됩니다.

최종 쿼리를 확인해보겠습니다.

team_name이 중복되므로 distinct처리를 해주고,

stuff와 for xml path를 활용해 문자열을 이어붙여 주었습니다.

STUFF는 제일 앞에 붙는 컴마 값을 삭제해 줍니다.

아래 쿼리를 실행하면, 

SELECT DISTINCT team_name, STUFF(( SELECT ','+player FROM #TEMP_TABLE FOR XML PATH('') ),1,1,'') as playerList FROM #TEMP_TABLE

최종 결과를 확인 할 수 있습니다.

, 가 아닌 다른 구분자를 활용하고 싶다면 SELECT 다음에 오는 구분자를 원하는 구분자로 바꿔 주기만 하면 됩니다.