Oct 6, 2015

Turning a Comma Separated string into individual rows in SQL Server



Problem: A column have string comma separated value as below.










We need result like as below

















Answer: See following query it may help you..
CREATE TABLE #TMP(ID INT, NAME VARCHAR(MAX))
INSERT #TMP SELECT 1, 'Dilip,Anil,Raj'
INSERT #TMP SELECT 2, 'Mahesh,Umesh'
INSERT #TMP SELECT 3, 'Rupesh,Uma,Swati'
INSERT #TMP SELECT 4, ''
SELECT * FROM #TMP
;WITH TMP(ID, ITEMS, NAME) AS (
SELECT ID , LEFT(NAME, CHARINDEX(',',NAME+',')-1),
    STUFF(NAME, 1, CHARINDEX(',',NAME+','), '')
FROM #TMP
UNION ALL
SELECT ID, LEFT(NAME, CHARINDEX(',',NAME+',')-1),
    STUFF(NAME, 1, CHARINDEX(',',NAME+','), '')
FROM TMP
WHERE NAME > ''
)
SELECT ID, ITEMS
FROM TMP
ORDER BY ID

DROP TABLE #TMP

It will return result as above given table pic....

Same as you can use given below query. It will also return same result as above


CREATE TABLE #TMP(ID INT, NAME VARCHAR(MAX))
INSERT #TMP SELECT 1, 'Dilip,Anil,Raj'
INSERT #TMP SELECT 2, 'Mahesh,Umesh'
INSERT #TMP SELECT 3, 'Rupesh,Uma,Swati'
INSERT #TMP SELECT 4, ''
SELECT * FROM #TMP

SELECT A.id, 
     Split.a.value('.', 'VARCHAR(100)') AS Data 
 FROM 
 (
     SELECT id, 
         CAST ('<M>' + REPLACE(NAME, ',', '</M><M>') + '</M>' AS XML) AS Data 
     FROM  #TMP
 ) AS A CROSS APPLY Data.nodes ('/M') AS Split(a);

DROP TABLE #TMP