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로 변경
