Problem: A column has a string comma separated value as below.
Answer: See the 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 the result as above given table pic.
Same as you can use given below query. It will also return the same result as above
Same as you can use given below query. It will also return the 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
|
