Posted by Uri Dimant on 03/22/06 09:59

Hi
I'd prefer doing such things on the client side
create table w
(
id int,
t varchar(50)
)

insert into w values (1,'abc')
insert into w values (1,'def')
insert into w values (1,'ghi')
insert into w values (2,'ABC')
insert into w values (2,'DEF')
select * from w


create function dbo.fn_my ( @id int)
returns varchar(100)
as
begin
declare @w varchar(100)
set @w=''
select @w=@w+t+',' from w where id=@id
return @w
end

select id,
dbo.fn_my (dd.id)
from
(
select distinct id from w
)
as dd

drop function dbo.fn_my


<uspensky@gmail.com> wrote in message
news:1142987822.013048.82870@v46g2000cwv.googlegroups.com...
>I have two tables:
>
> tb_news--
> -story_id
> -productlist
>
> tb_lookup--
> -story_id
> -product
>
> when an insert command is run on tb_news, productlist field is
> populated with a value such as 'abc, def, de'
>
> when this happens, i need tb_lookup to be populated with seperate
> records for each product in productlist and the story_id from tb_news.
>
> Example:
> INSERT INTO tb_news (story_id, product_list)
> VALUES (12345, 'abc, def, de')
>
> Results:
> tb_news--
> 12345, 'abc, def, de'
>
> tb_lookup--
> 12345, 'abc'
> 12345, 'def'
> 12345, 'de'
>
>
> Ideally, I would like this to use recursion and give me tha ability to
> change the delimiter at any time (might not always be a comma). some
> products may have a period in them. number of products is unknown and
> might be 0 (field may be empty or NULL).
>

[Back to original message]