/* ----------------------------------------------------------------------------------------------------
특정 문자로 구분된 값을 행으로 표현
- sqler 에 질문이 있어서 쿼리 작성~
---------------------------------------------------------------------------------------------------- */
WITH T1 AS
(
SELECT SCAN_CODE = '2017-09-11300042', CHILD_SCAN = ';2;3;4' UNION ALL
SELECT SCAN_CODE = '2017-09-11300045', CHILD_SCAN = ';5;6;7'
)
SELECT CONVERT(VARCHAR(MAX),T.c.query('text()'))
FROM (
SELECT CHILD_SCAN = CONVERT(XML, (REPLACE(('<R>' + REPLACE((SUBSTRING(CHILD_SCAN, 2, LEN(CHILD_SCAN))), ';', '</R><R>') + '</R>'), '<R>', '<R>' + SUBSTRING(SCAN_CODE, 1, LEN(SCAN_CODE) -1))))
FROM T1
) AS A
CROSS APPLY A.CHILD_SCAN.nodes('/R') AS T(c)
-- 질문 : http://www.sqler.com/975617#1
-- 참고 : http://catchv.tistory.com/18