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
: