How to do this:

How to do this:

I have a table ID, Desc

ID, Desc

1, xyz|zyx|abc

2, xbz|zvx|abc

3, xaz|zcx|dbc

How can you convert this to

ID,Desc

1 xyz

1 zyx

1 abc

2, xbz

2 zvx

2 abc

3, xaz

3 zcx

3 dbc

Hi. try this:

DECLARE @.Table TABLE (id int, [desc] varchar(10))
declare @.pos int
declare @.id int
declare @.description varchar(30)
declare @.desc varchar(10)

declare cur cursor for select id, [desc] from test
open Cur

fetch next from cur into @.id, @.description

while @.@.fetch_status = 0
begin
WHILE (LEN(@.description) > 1)
BEGIN
SET @.Pos = CHARINDEX('|', @.description)
SET @.desc = SUBSTRING(@.description, 0, @.Pos)
SET @.description = SUBSTRING(@.description, @.Pos + 1, LEN(@.description))
If (@.Pos = 0)
BEGIN
SET @.desc = @.description
SET @.description = ''
END
--Inserta la cuenta
INSERT INTO @.Table (id, [desc]) VALUES (@.id, @.desc)
END

fetch next from cur into @.id, @.description
end

close cur
deallocate cur

select * from @.table

|||

Or, you could write a function that takes in the ID and returns a table with the values parsed. HEre's some hint:

Declare @.ttable (IDint , Descrvarchar(50))insert into @.tvalues (1,'xyz|zyx|abc|dfg')insert into @.tvalues (2,'xbz|zvx|abc|sss')insert into @.tvalues (3,'xaz|zcx|dbc')select *from @.tdeclare @.t2table (col1varchar(10))declare @.iint, @.strvarchar(50)set @.i = 2select @.str = Descrfrom @.twhere Id = @.iwhile len(@.str) > 0beginif charindex('|', @.str) > 0begininsert into @.t2values (left(@.str, charindex('|', @.str)-1))set @.str =replace(@.str,left(@.str, charindex('|', @.str)),'')endelsebegininsert into @.t2values (@.str)set @.str =nullendendselect *from @.t2