[MSSQL] FOR XML 을 이용한, GROUP BY 시 목록 나열?

글 제목이 좀 이상하네요.

내용을 읽어보시면, 아, 이런 얘기구나 하실 겁니다.

다음 글은 SQL Server 2005 이상에서만 작동하는 것으로 압니다.

계속 SQL2000 호환 쿼리에 얽매여 스크립트를 짜다가, 최근에 2005 로 왔는데요, 이것저것 사용할 수 있는 게 많아서 좋습니다.

오늘은 FOR XML 구문을 활용하여, 실 사용시 필요할 수도 있는(?) 쿼리를 알아보겠습니다.

이런 경우가 있습니다. GROUP BY 를 이용하여, 그룹화 할 때, 나머지 항목들에 대해 한 컬럼에 주르륵 나열 하고 싶은 경우. 예전에는(SQL2000) TEMP 테이블을 사용해서 문자열을 막 이어붙이는 중노동을 했습니다만, FOR XML 구문을 이용하여 한 쿼리로 만들 수가 있네요.

참고 : FOR XML (MSDN)

SET NOCOUNT ON;

-- 여기에서 부터,
CREATE TABLE #TEMP_H
(
    JUMUN_NUMBER   NVARCHAR(20),
    CUSTOMER_NAME  NVARCHAR(30)
);

CREATE TABLE #TEMP_D
(
    JUMUN_NUMBER   NVARCHAR(20),
    JUMUN_SEQ      INT,
    GOODS_NAME     NVARCHAR(30),
    JUMUN_AMOUNT   NUMERIC(17)
);

INSERT INTO #TEMP_H VALUES ( 'JM20130628-001', '전두환' );
INSERT INTO #TEMP_D VALUES ( 'JM20130628-001', 1, '짜장면', 100000);
INSERT INTO #TEMP_D VALUES ( 'JM20130628-001', 2, '짬뽕', 100000);
INSERT INTO #TEMP_D VALUES ( 'JM20130628-001', 2, '군만두', 90000);

INSERT INTO #TEMP_H VALUES ( 'JM20130628-002', '노태우' );
INSERT INTO #TEMP_D VALUES ( 'JM20130628-002', 1, '짜장면', 5000);
INSERT INTO #TEMP_D VALUES ( 'JM20130628-002', 2, '짬뽕', 6000);
-- 여기까지는 그냥 데이터 만드는 겁니다. 신경쓰지 마셔요.

SELECT H.JUMUN_NUMBER AS [주문번호],
       MAX(H.CUSTOMER_NAME) AS [주문자],
       SUM(D.JUMUN_AMOUNT) AS [총금액],
       RTRIM( ( SELECT D2.GOODS_NAME + ' '
                  FROM #TEMP_D D2
                 WHERE D2.JUMUN_NUMBER = H.JUMUN_NUMBER
                FOR XML PATH ('') ) ) AS [주문하신 목록]
  FROM #TEMP_H H INNER JOIN #TEMP_D D ON D.JUMUN_NUMBER = H.JUMUN_NUMBER
 GROUP BY H.JUMUN_NUMBER;
  
DROP TABLE #TEMP_D;
DROP TABLE #TEMP_H;

SET NOCOUNT OFF;

결과는 다음과 같습니다.

자세한 설명은, MSDN 참고하시면 좋을 것 같은데요, 첫 머리에 있는 글이 핵심이네요.

SELECT 쿼리는 결과를 행 집합으로 반환합니다. 선택적으로 쿼리에서 FOR XML 절을 지정하면 SQL 쿼리의 서식 결과를 XML로 검색할 수 있습니다.(번역본이라 그런데, 수정해서 말하자면, XML 화 할 수 있습니다. 가 맞을 듯 싶습니다.)