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?
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 | +------+ Hope this will help you out.