Turning a Comma Separated string into individual rows in SQL Server

Problem: A column has a string comma separated value as below.

We need a result like 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



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

 

PostgreSQL-Query: Sort result set by specific field values using ORDER BY Clause

Problem: Suppose we have a book_inventory table which has some columns such as id, isbn, title, author, publisher, publish_date, etc.. whe...