[MSSQL] 여러 행 값을 한 컬럼에 넣기

 데이터 집계를 하다 보면 여러 값을 한 개의 컬럼에 넣어야 하는 경우가 발생합니다.

 

ex #1. 2022년에 각 팀별 평가 평균과 해당 팀원 목록을 한 줄로 보여주세요.

ex #2. 택배 송장에 붙일 전체 주문 금액과 해당 상품 목록을 한 줄로 보여주세요.

ex #3. 현재 공장에서 생산 중인 제품 목록을 한 줄로 보여주세요.

 

 여기에 동일한 내용은 데이터를 취합해서 한 줄로 보여 줘야 하는 경우입니다. 우선 예시에 앞서 아래의 데이터를 추가해줍니다.

 

CREATE TABLE STUDENT (
SEQ		DECIMAL(18,0),
CLASS	VARCHAR(10),
NAME	VARCHAR(10)
)

INSERT INTO STUDENT (SEQ, CLASS, NAME) VALUES (1, '1반', '호두')
INSERT INTO STUDENT (SEQ, CLASS, NAME) VALUES (2, '1반', '금돌이')
INSERT INTO STUDENT (SEQ, CLASS, NAME) VALUES (3, '2반', '뽀삐')
INSERT INTO STUDENT (SEQ, CLASS, NAME) VALUES (4, '2반', '소미')
INSERT INTO STUDENT (SEQ, CLASS, NAME) VALUES (5, '3반', '로이&')

해당 예시는 Student라는 정보를 만들어보았습니다.

여기서 seq는 학번이고, class는 반, name는 학생 이름이라고 보면 됩니다.

 

만약, 각 반에 있는 학생의 정보를 한 줄로 보여줘야 한다면, 아래와 같이 작성하면 됩니다.

SELECT T1.CLASS
	 , STUFF((SELECT ',' + ST1.NAME FROM STUDENT ST1 WHERE ST1.CLASS = T1.CLASS FOR XML PATH('')), 1, 1, '')	NAMES
  FROM STUDENT AS T1
 GROUP BY T1.CLASS

보시는 바와 같이 각 반에 학생 이름이 포함되었음을 알 수 있습니다.

구분자는 Sub Query의 Select 다음의 ','가 구분자가 됩니다.

그리고, 뒤에 1, 1이 첫 번째 행의 경우 해당 범위만큼 값을 제외해주는 역할로, 호두 / 뽀삐, 로이 앞의 콤마(,)가 삭제되었음을 확인하실 수 있습니다.

그런데 여기에서, "로이&"의 정보가 &로 표시되는데, 이는 xml형태로 표시되었기 때문입니다. 이를 해결하기 위해서는 아래와 같이 Query를 수정해주시면 됩니다.

SELECT T1.CLASS
	 , STUFF((SELECT ',' + ST1.NAME FROM STUDENT ST1 WHERE ST1.CLASS = T1.CLASS FOR XML PATH(''), root('MyString'), type).value('/MyString[1]','Nvarchar(max)'), 1, 1, '')	NAMES
  FROM STUDENT AS T1
 GROUP BY T1.CLASS