Tara D (2/13/2013)

---

I have a table that has

ID Name
123 12345-abcd, 6789-efgh
456 R11223344-abc, 223366-rreett, A45566-ppooit

I need to take the "name" column and split it to muliple columns whenever I find a comma (,). I have found functions that do this, but they return a table variable. I need something that I can use that when used I can retain the relationship between the split columns and the ID columns. Can anyone lend a hand?

Thanks!

sqlbi.vvamsi (2/13/2013)

---

perhaps this might help:

declare @tbl table (id int,string varchar(512))
Insert into @tbl
SELECT 123,
'12345-abcd, 6789-efgh'
UNION ALL
SELECT 456,
'R11223344-abc, 223366-rreett, A45566-ppooit'
select * from @tbl

;with cte(id,position,string,remaining)
as
(
select id,charindex(',',string), left(string,charindex(',',string)-1),ltrim(right(string,len(string)-charindex(',',string)))from @tbl
union all
select id,charindex(',',remaining),case when charindex(',',remaining)=0 then ltrim(right(remaining,len(remaining)-charindex(',',remaining))) else left(remaining,charindex(',',remaining)-1) end,ltrim(right(remaining,len(remaining)-charindex(',',remaining)))from cte where string!=remaining
)
select id,string from cte order by id