Retrieving CSV values in a single row as multiple rows in Sql Server 2008

Question

I have a table in my database as follows:

+-------+
| Name  |
+-------+
| A     |
| B     |
| C,D   |
| A,B,E |
+-------+

I want the output as

+------+
| Name |
+------+
| A    |
| B    |
| C    |
| D    |
| E    |
+------+

My question is how to retrieve C,D and A,B,E into different rows?

Answer 1

First create a custom split() function.

Split Function

CREATE FUNCTION dbo.Split(@String nvarchar(4000), @Delimiter char(1))
RETURNS @Results TABLE (colA nvarchar(4000))
AS
BEGIN
DECLARE @INDEX INT
DECLARE @SLICE nvarchar(4000)

SELECT @INDEX = 1
WHILE @INDEX !=0
BEGIN

SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
IF @INDEX !=0

SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
ELSE
SELECT @SLICE = @STRING

INSERT INTO @Results(colA) VALUES(@SLICE)

SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)

IF LEN(@STRING) = 0 BREAK
END
RETURN
END

Table

CREATE TABLE names(name VARCHAR(MAX));

INSERT INTO names VALUES('A');
INSERT INTO names VALUES('B');
INSERT INTO names VALUES('C,D');
INSERT INTO names VALUES('A,B,E');

SELECT * FROM names;

Table Structure

+-------+
| name  |
+-------+
| A     |
| B     |
| C,D   |
| A,B,E |
+-------+

Then assign all the values as comma separated to a variable.

DECLARE @str VARCHAR(MAX)

SELECT @str=t.csv FROM (SELECT SUBSTRING(
(SELECT ',' + n.name
FROM names n
ORDER BY n.name
FOR XML PATH('')),2,200000) AS csv)t;

SELECT DISTINCT colA AS Name FROM Split(@str,',');

Result

+------+
| Name |
+------+
|  A   |
|  B   |
|  C   |
|  D   |
|  E   |
+------+

Fiddle Demo Here

Hope this will help you out.