Sean Lange (6/19/2014)

---

Ed Pollack (6/19/2014)

---

raghava_tg (6/18/2014)

---

create table #temp (id int, data varchar(80))

insert into #temp (id, data)

select 1, 'a,b,c'

union all

select 2, 'x,y'

union all

select 3,'j,k,l'

select * from #temp

i want to create a output like this.

1 a

1 b

1 c

2 x

2 y

3 j

3 k

3 l

wanted to create table with above desired result.

the values in second column is comma separated value.

please help me with possible solution.

One way to do this would be with a table-valued function such as this, which uses XML to parse through your comma-separated list:

CREATE FUNCTION dbo.comma_split (@input_string VARCHAR(MAX))

RETURNS @Result TABLE(Value VARCHAR(25))

AS

BEGIN

DECLARE @x XML

SELECT @x = CAST('<A>'+ REPLACE(@input_string,',','</A><A>')+ '</A>' AS XML)

INSERT INTO @Result

SELECT t.value('.', 'char') AS inVal

FROM @x.nodes('/A') AS x(t)

RETURN

END

GO

From here, you can call the function like this

SELECT

TMP.id,

CSD.Value

FROM #temp TMP

CROSS APPLY dbo.comma_split(TMP.data) CSD

:

Ed you should take a look at the DelimitedSplit8K function. It is way faster than the xml style splitter. 😉