//MSSQL STUFF 함수와 FOR XML PATH 를 이용하여 여러 ROW 의 값을 하나의 ROW 로 합치기

STUFF 함수
SUTFF 함수는 문자열에서 특정 시작 위치에서 지정된 길이만큼 문자를 반환하여 값을 생성해준다.

SELECT STUFF('ABCDEFGH', 1, 1, '') -- 1부터 1+1까지 값을 지운다는 의미
-- 결과
-- BCDEFGH

SELECT STUFF('ABCDEFGH', 2, 3, '') -- 2부터 2+3까지 값을 지운다는 의미
-- 결과
-- AEFGH

SELECT STUFF('ABCDEFGH', 2, 3, 'XYZ') -- 2부터 2+3까지 값을 지우고 그 위치에 XYZ 를 넣는다는 의미
-- 결과
-- AXYZEFGH

FOR XML PATH

FOR XML PATH 기능은 여러 ROW 의 값을 하나의 ROW 로 합치는 기능을 한다.
 - ORDER BY 를 이용하면 ORDER BY 순서에 맞게 ROW 의 값들이 합쳐진다.
 

SELECT '-' + CLASS
  FROM PRAC.DBO.TB_AD_OPER_PLAN
 ORDER BY CLASS -- ORDER BY
   FOR XML PATH('')
   ;
-- 결과
-- _A_B_C_A_B_C_D_E_F_G

문제

 

DROP TABLE PRAC.DBO.TB_AD_OPER_PLAN
CREATE TABLE PRAC.DBO.TB_AD_OPER_PLAN
( OPER_CD  NVARCHAR(50)
, PLANT_CD NVARCHAR(50)
, CLASS    NVARCHAR(50)
)
INSERT INTO PRAC.DBO.TB_AD_OPER_PLAN
SELECT 'O_001' AS [OPER_CD], 'P001' AS [PLANT_CD], 'A' AS [CLASS]  UNION ALL
SELECT 'O_001' AS [OPER_CD], 'P001' AS [PLANT_CD], 'B' AS [CLASS]  UNION ALL
SELECT 'O_001' AS [OPER_CD], 'P001' AS [PLANT_CD], 'C' AS [CLASS]  UNION ALL
SELECT 'O_001' AS [OPER_CD], 'P002' AS [PLANT_CD], 'A' AS [CLASS]  UNION ALL
SELECT 'O_001' AS [OPER_CD], 'P002' AS [PLANT_CD], 'B' AS [CLASS]  UNION ALL
SELECT 'O_001' AS [OPER_CD], 'P002' AS [PLANT_CD], 'C' AS [CLASS]  UNION ALL
SELECT 'O_001' AS [OPER_CD], 'P002' AS [PLANT_CD], 'D' AS [CLASS]  UNION ALL
SELECT 'O_001' AS [OPER_CD], 'P002' AS [PLANT_CD], 'E' AS [CLASS]  UNION ALL
SELECT 'O_001' AS [OPER_CD], 'P002' AS [PLANT_CD], 'F' AS [CLASS]  UNION ALL
SELECT 'O_001' AS [OPER_CD], 'P002' AS [PLANT_CD], 'G' AS [CLASS] 
;

SELECT * FROM PRAC.DBO.TB_AD_OPER_PLAN;

SELECT OPER_CD
     , PLANT_CD
     , CLASS
     , STUFF((SELECT '-' + CLASS
                FROM PRAC.DBO.TB_AD_OPER_PLAN
               WHERE OPER_CD   = A.OPER_CD
                 AND PLANT_CD  = A.PLANT_CD
                 AND CLASS    <= A.CLASS
               ORDER BY CLASS
                 FOR XML PATH('')
              ), 1, 1, '') MAP_CLASS
  FROM PRAC.DBO.TB_AD_OPER_PLAN A
  ;

* 참고
http://blog.sonim1.com/107