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. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/