[MSSQL] 여러개 행을 하나의 행으로 합치기 (STUFF, XML PATH)

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 다음에 오는 구분자를 원하는 구분자로 바꿔 주기만 하면 됩니다.