MSSQL STUFF, FOR XML PATH

STUFF 와 FOR XML PATH  테스트 !

CREATE TABLE #TABLE1 (T1_ID INT)

CREATE TABLE #TABLE2 (T2_ID INT, T1_ID INT)

INSERT INTO #TABLE1 VALUES(1) -- 테이블 생성
INSERT INTO #TABLE1 VALUES(2)

INSERT INTO #TABLE2 VALUES(1, 1) -- 테이블 값 생성
INSERT INTO #TABLE2 VALUES(2, 1)
INSERT INTO #TABLE2 VALUES(3, 1)
INSERT INTO #TABLE2 VALUES(4, 2)
INSERT INTO #TABLE2 VALUES(5, 2)
INSERT INTO #TABLE2 VALUES(6, 2)

SELECT * FROM #TABLE1 -- 테스트 준비 완료
SELECT * FROM #TABLE2

테스트 시작 - TABLE2의 데이터를 한 ROW로 나타냄 : 결과

SELECT  T3.T1_ID,  STUFF( ( SELECT ',' + CAST(T2.T2_ID AS CHAR) FROM #TABLE1 AS T1          INNER JOIN #TABLE2 AS T2 ON T1.T1_ID = T2.T1_ID   WHERE T1.T1_ID = T3.T1_ID GROUP BY T2.T2_ID, T2.T1_ID ORDER BY T2.T1_ID FOR XML PATH('')  )  , 1, 1, '') AS T2_ID  FROM #TABLE1 AS T3  WHERE T3.T1_ID IN (1,2)  GROUP BY T3.T1_ID

STUFF 제거 : 앞에 , 살아남

SELECT T3.T1_ID,  --STUFF(  ( SELECT ',' + CAST(T2.T2_ID AS CHAR) FROM #TABLE1 AS T1          INNER JOIN #TABLE2 AS T2 ON T1.T1_ID = T2.T1_ID   WHERE T1.T1_ID = T3.T1_ID GROUP BY T2.T2_ID, T2.T1_ID ORDER BY T2.T1_ID FOR XML PATH('') ) AS T2_ID   --  , 1, 1, '') AS T2_ID  FROM #TABLE1 AS T3  WHERE T3.T1_ID IN (1,2)  GROUP BY T3.T1_ID

 실제 여러 ROW의 데이터를 ,로 구분하는 역할은 FOR XML PATH('') 가 수행한다.

안의 SELECT 쿼리 테스트 결과 

SELECT ',' + CAST(T2.T2_ID AS VARCHAR) AS T2_ID  FROM #TABLE1 AS T1  INNER JOIN #TABLE2 AS T2 ON T1.T1_ID = T2.T1_ID   --WHERE T1.T1_ID = T3.T1_ID  GROUP BY T2.T2_ID, T2.T1_ID  ORDER BY T2.T1_ID

SELECT ',' + CAST(T2.T2_ID AS VARCHAR)  FROM #TABLE1 AS T1   INNER JOIN #TABLE2 AS T2 ON T1.T1_ID = T2.T1_ID  --WHERE T1.T1_ID = T3.T1_ID  GROUP BY T2.T2_ID, T2.T1_ID  ORDER BY T2.T1_ID  FOR XML PATH('')

SELECT ',' + CAST(T2.T2_ID AS VARCHAR)  AS TAG  FROM #TABLE1 AS T1   INNER JOIN #TABLE2 AS T2 ON T1.T1_ID = T2.T1_ID   --WHERE T1.T1_ID = T3.T1_ID  GROUP BY T2.T2_ID, T2.T1_ID  ORDER BY T2.T1_ID  FOR XML PATH('')

- STUFF

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

EX) STUFF('ABCDEF', 2, 2 , 'abc') => 'AabcDEF'
두번째부터 시작하여, 2 길이의 문자를 abc로 변경