[MS-SQL] 컬럼 합치는 방법 3가지(STUFF, STRING_AGG)

데이터베이스에서 쿼리를 실행하면 결과값은 보통 여러 행으로 나오게 됩니다.

컬럼으로 나오면 컬럼끼리 합치는 게 간단하지만 여러 행으로 나온 같은 컬럼을 합치기 위해서는 별도로 처리를 해줘야 합니다.

통계페이지 같은 경우 은근히 사용하게 되는 거 같네요.

함수를 사용하는 방법도 있고, 반복구문을 돌려서 하는 방법도 있고 해서 정리해 봅니다.

 

 

 

먼저 테스트 할 임시테이블과 데이터를 만들겠습니다. 필요없으신 분은 바로 아래로 스크롤 하세요~~ㅋ

CREATE TABLE #TMP (
    Teams       NVARCHAR(10),
    PlayerName    NVARCHAR(10),
    BackNo      INT,
    IsRetire    CHAR(1)
)

INSERT INTO #TMP VALUES('LG', '김현수', 22, 0)
INSERT INTO #TMP VALUES('LG', '박용택', 33, 1)
INSERT INTO #TMP VALUES('키움', '이정후', 51, 0)
INSERT INTO #TMP VALUES('두산', '박철순', 21, 1)
INSERT INTO #TMP VALUES('LG', '정우영', 18, 0)
INSERT INTO #TMP VALUES('두산', '이승엽', 77, 0)
INSERT INTO #TMP VALUES('LG', '고우석', 19, 0)
INSERT INTO #TMP VALUES('키움', '김혜성', 3, 0)
INSERT INTO #TMP VALUES('두산', '양의지', 25, 0)

 

이렇게 임시테이블을 만들어서 SELECT로 결과값을 보면,

아마도 이렇게 나오겠죠..??

 

원하는 결과값은 팀별로 은퇴하지 않은 소속선수와 그 선수의 등번호를 추출하는 것입니다.

3가지 방법을 진행 해 볼 생각인데요.. 성능까지는 잘 모르니 직접 확인해 주세요.. 저는 결과값 뽑는 것 만으로도 벅차서...ㅎㅎ

 

1. 반복문 활용

반복문을 사용하면 코드가 길어지는 단점이 있긴 한데, 별 고민없이 만들기는 괜찮은거 같네요.

DECLARE @TBL Table(Teams NVARCHAR(10), Players    NVARCHAR(MAX))
SELECT Teams, 
       PlayerName + '(' + CONVERT(VARCHAR, BackNo) + ')' AS Players, 
       Row_Number() OVER(Partition by Teams Order by PlayerName ) AS CNT
  INTO #Temp
  FROM #TMP
 WHERE IsRetire = 0 

DECLARE @Cnt INT = 1 

WHILE (1=1)
BEGIN
    IF @Cnt = 1
    BEGIN
        INSERT @TBL
        SELECT Teams, Players 
          FROM #Temp
         WHERE CNT = @Cnt
    END
    ELSE
    BEGIN
        UPDATE @TBL
           SET Players = A.Players + ', ' + B.Players
          FROM @tbl A JOIN #Temp B ON A.Teams = B.Teams
         WHERE B.CNT = @CNT

        IF @@RowCount = 0 BREAK
    END
    SELECT @CNT = @CNT + 1
END

먼저 원하는 결과값의 대상들만 @TBL에 담아놓고, 반복해서 돌면서 업데이트 하는 구문입니다.

 

2. STUFF 

먼저 STUFF 사용법을 정리하면,

STUFF( '대상문자열', 시작위치, 크기, '변경할문자' )

 

그리고 STUFF를 이용한 쿼리입니다. 반복문을 쓸때 보다는 훨씬 간단하네요.

SELECT DISTINCT Teams, 
        STUFF( (SELECT ', ' + PlayerName + '(' + CONVERT(VARCHAR, BackNo) + ')'
                  FROM #TMP
                 WHERE IsRetire = 0
                   AND A.Teams = Teams
                 ORDER BY PlayerName
                   FOR XML PATH('')), 1, 2, '') AS Players

FOR XML PATH가 데이터를 하나로 다 합치게 됩니다.

그리고 나서 맨 앞에 붙은  ', '를 STUFF를 이용해서 ''로 치환해서 없애버렸습니다.

이전에 가장 많이 사용하던 방식입니다.

 

3. STRING_AGG

마지막으로 STRING_AGG인데요. 이 방법은 MS-SQL 2017버전 부터 사용 가능합니다. 

이전 버전에서는 작동하지 않으니 참고하시기 바랍니다.
STRING_AGG 사용법은

STRING_AGG('합칠컬럼명', '구분자' ) WITHIN GROUP(ORDER BY '컬럼명')

 

STRING_AGG를 사용한 코드는 제일 간단하네요. 역시 최신 버전!!!!

SELECT Teams, 
       STRING_AGG (PlayerName + '(' + CONVERT(VARCHAR, BackNo) + ')', ', ') WITHIN GROUP(ORDER BY PlayerName) AS Players
  FROM #TMP
 WHERE IsRetire = 0
 GROUP BY Teams

 

이렇게 위의 3가지 방법을 다 실행하면.... 당연히 결과값은 동일하게 나옵니다..

아래의 그림 처럼...

이렇게 나오게 됩니다.(2022년 12월 현재 FA결과를 포함했습니다..ㅎㅎㅎ)