The requirement is to convert the delimited column into rows
Input data and required output details are given below
Eno | Ename | Esloc | EDept |
1 | ABC | NJ | 10,20,30,40,50 |
Output:-
Eno | Ename | Esloc | EDept |
1 | ABC | NJ | 10 |
1 | ABC | NJ | 20 |
1 | ABC | NJ | 30 |
1 | ABC | NJ | 40 |
1 | ABC | NJ | 50 |
Download the Script Split Delimited Column using XML Or UDF
T-SQL -Split a delimited column data using XML
DECLARE @DemoTable table ( Eno int, Ename char(10), EsLoc char(10), EDept varchar(20)) INSERT INTO @DemoTable values(1,'Prashanth','NJ','1,2,3,4,5') INSERT INTO @DemoTable values(2,'Jayaram','NY','1,2,3') SELECT A.ENo, A.EName , A.EsLoc, Split.a.value('.', 'VARCHAR(100)') AS Dept FROM (SELECT ENo,EName,EsLoc, CAST ('<M>' + REPLACE(EDept, ',', '</M><M>') + '</M>' AS XML) AS String FROM @DemoTable) AS A CROSS APPLY String.nodes ('/M') AS Split(a);
OR
Using UDF-
Reference Link :- http://visakhm.blogspot.in/2010/02/parsing-delimited-string.html
CREATE FUNCTION ParseValues (@String varchar(8000), @Delimiter varchar(10) ) RETURNS @RESULTS TABLE (ID int identity(1,1), Val varchar(8000)) AS BEGIN DECLARE @Value varchar(100) WHILE @String is not null BEGIN SELECT @Value=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN LEFT(@String,PATINDEX('%'+@Delimiter+'%',@String)-1) ELSE @String END, @String=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN SUBSTRING(@String,PATINDEX('%'+@Delimiter+'%',@String)+LEN(@Delimiter),LEN(@String)) ELSE NULL END INSERT INTO @RESULTS (Val) SELECT @Value END RETURN END DECLARE @DemoTable table ( Eno int, Ename char(10), EsLoc char(10), EDept varchar(20)) INSERT INTO @DemoTable values(1,'Prashanth','NJ','1,2,3,4,5') INSERT INTO @DemoTable values(2,'Jayaram','NY','1,2,3') SELECT t.Eno, t.Ename,t.EsLoc,f.Val Dept FROM @DemoTable t CROSS APPLY dbo.ParseValues(t.EDept,',')f
Output:-