프로그램 자료/MS-SQL
[MS-SQL] 구분자로 된 칼럼을 세로로 출력하기 split 함수처럼
motolies
2014. 4. 3. 14:35
출처 : http://www.sqler.com/?mid=bSQLQA&listStyle=webzine&document_srl=407559
IF OBJECT_ID('tbl') IS NOT NULL
DROP TABLE tbl
GO
CREATE TABLE tbl
(
idx int ,
value VARCHAR(MAX)
)
GO
INSERT INTO tbl
SELECT 1, ' 123 , 456 , 789 , 321 '
GO
SELECT a.idx ,LTRIM(RTRIM(SUBSTRING(a.value,b.s,b.e-b.s))) AS Split
FROM tbl a
CROSS APPLY ( SELECT number AS s, CHARINDEX(',',a.value+',',number+1) AS e
FROM master..spt_values
WHERE number
= CHARINDEX(',',','+a.value,number)
AND type
='P' ) b
GO