[MSSQL] 여러 행 값을 하나의 행에 나열하는 방법

한 컬럼에 속한 데이터를 한 칸에 표기해서 보여줘야 할때가 있다.

 

 

 

> 테이블 명칭 : ABC_TABLE

 

            ↓

 

 

 

총 두가지 방법이 있다.

 

1. STUFF, FOR XML PATH (SQL Sever 2017 이전 버전)

2. STRING_AGG (SQL Server 2017 이상 버전)

 

1. STUFF, FOR XML PATH

먼저 FOR XML PATH 사용 예시는 아래와 같다.

SELECT COLUMN1 FROM ABC_TABLE FOR XML PATH  실행 

<row><COLUMN1>A</COLUMN1></row>

<row><COLUMN1>B</COLUMN1></row>

<row><COLUMN1>C</COLUMN1></row>

<row><COLUMN1>D</COLUMN1></row>

<row><COLUMN1>E</COLUMN1></row> 

→ 행 집합의 각 행마다 <row> 태그와 행 요소 이름(여기서는 <COLUMN1>)을 갖는다.

 

SELECT COLUMN1 FROM ABC_TABLE FOR XML PATH('') 실행

<COLUMN1>A</COLUMN1>

<COLUMN1>B</COLUMN1>

<COLUMN1>C</COLUMN1>

<COLUMN1>D</COLUMN1>

<COLUMN1>E</COLUMN1>

→ PATH에서 <row> 이름을 대체하는 문자로 길이가 0인 문자('')를 추가함.

 

SELECT ',' + COLUMN1 FROM ABC_TABLE FOR XML PATH('') 실행

→ SELECT할때 컬럼 앞에 ',' 를 붙여주면서 이름이 없는 열이 되어서 행 요소 이름은 생략되어 ,A,B,C,D,E만 표기된다.

 

이제 여기서 ,A,B,C,D,E -> A,B,C,D,E 로 바꿔주면 된다.

이때 사용하는 함수가 STUFF 함수이다.

STUFF 함수란, 문자열에서 특정 위치에 있는 문자를 원하는 문자로 치환해주는 것이다.

STUFF ( 문자열, 시작위치, 치환할 문자 길이, 치환할 문자)

위에서 구한 ,A,B,C,D,E 문자에서 제일 첫번째 문자에서 길이가 1인 문자를 없는 문자로 치환해야 한다.

SELECT STUFF((SELECT ',' + COLUMN1 FROM ABC_TABLE FOR XML PATH('')),1,1,'') AS COLUMN1 

FROM ABCTABLE 실행

A,B,C,D,E 각각의 행에 모두 나오기때문에 중복을 제거해주기 위해서 DISTINCT를 추가한다.

 

SELECT DISTINCT STUFF((SELECT ',' + COLUMN1 FROM ABC_TABLE FOR XML PATH('')),1,1,'') AS COLUMN1 

FROM ABCTABLE 실행

 

2. STRING_AGG

내가 글을 쓰게 된 이유가 인터넷 찾아보니 두개가 같이 있는 글이 드물고 대부분 STUFF, FOR XML PATH 만 있어서이다..

STRING_AGG 는 문자열 식의 값을 연결하고 그 사이에 구분 기호 값을 추가하면서 구분 기호는 문자열 끝에 추가되지 않는다.

STRING_AGG ( 표현식, 구분기호 )

 

SELECT STRING_AGG(COLUMN1, ',') AS COLUMN1 FROM ABC_TABLE 실행

1번 하면서 구구절절 힘들게 설명하던 것이 STRING_AGG 로 한번에 해결되었다..

자세한 내용은 아래 참고자료에서 참고 바란다.

 

 

참고자료 :

https://docs.microsoft.com/ko-kr/sql/relational-databases/xml/columns-without-a-name?view=sql-server-ver15 

https://docs.microsoft.com/ko-kr/sql/relational-databases/xml/examples-using-path-mode?view=sql-server-ver15

https://docs.microsoft.com/ko-kr/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver15