query contains concatenated inner results

I have a table item(id, item_name, item_added_date, item_price).
The second table is item_viewedby(id, item_id, userid).
The second table holds the users who are viewing a certain item.
So data in first table item is like:
id item_name item_added_date item_price
1 toothbrush 01/01/2003 100
2 toothpaste 02/02/2003 125
And data in second table item_viewedby is like:
id item_id userid
1 1 jane
2 1 john
3 1 kim
4 2 jim
5 2 tim
I want to execute a query so that for each item I have a single row of
item_id item_price item_viewed_by
Where item_viewed_by is a semi_colon separated list of users who are
viewing a certain item.
So my result table is like
item_id item_price item_viewed_by
1 100 jane;john;kim
2 125 jim;tim
I am wanting to put this query in a stored procedure and call it from
my ASP.NET code.
If this is to be done using cursors then please help me with that, too
Thanks,
FredSee the following example which will give you a fair idea how can you
convert rows to columns.
Ex:
create table tab(ID int,
cats varchar(50))
go
insert into tab values(1 ,'jane')
insert into tab values(1 ,'john')
insert into tab values(1 ,'kim')
insert into tab values(2 ,'nick')
insert into tab values(3 ,'jim')
insert into tab values(3 ,'tim')
go
drop table #tmp
create table #tmp(id int, tmpval varchar(50))
go
declare @.id int, @.old_id int
declare @.seq_num int
declare @.cats varchar(50), @.f_cats varchar(50)
select @.id=0, @.old_id=0,@.seq_num=0, @.cats='', @.f_cats=''
declare c1 cursor for
select id, cats from tab order by id
open c1
fetch c1 into @.id,@.cats
while @.@.fetch_status = 0
begin
If @.old_id <> @.id and @.old_id <> 0
begin
insert into #tmp values(@.old_id, @.f_cats)
select @.seq_num=0, @.f_cats=''
end
select @.f_cats = @.f_cats + case @.f_cats when '' then '' else ',' end + @.cats
select @.old_id = @.id
fetch c1 into @.id,@.cats
end
close c1
deallocate c1
insert into #tmp values(@.old_id, @.f_cats)
select * from #tmp
--
-Vishal
"Fred" <fredg1232003@.yahoo.com> wrote in message
news:2022fcea.0307281714.3529f2f@.posting.google.com...
> I have a table item(id, item_name, item_added_date, item_price).
> The second table is item_viewedby(id, item_id, userid).
> The second table holds the users who are viewing a certain item.
> So data in first table item is like:
> id item_name item_added_date item_price
> 1 toothbrush 01/01/2003 100
> 2 toothpaste 02/02/2003 125
> And data in second table item_viewedby is like:
> id item_id userid
> 1 1 jane
> 2 1 john
> 3 1 kim
> 4 2 jim
> 5 2 tim
> I want to execute a query so that for each item I have a single row of
> item_id item_price item_viewed_by
> Where item_viewed_by is a semi_colon separated list of users who are
> viewing a certain item.
> So my result table is like
> item_id item_price item_viewed_by
> 1 100 jane;john;kim
> 2 125 jim;tim
> I am wanting to put this query in a stored procedure and call it from
> my ASP.NET code.
> If this is to be done using cursors then please help me with that, too
> Thanks,
> Fred|||yes you can review it..........
if you get any issues just inform.
regards
Hari Sharma, India
Wangkhar@.yahoo.com (WangKhar) wrote in message news:<bb269444.0308040106.63f76743@.posting.google.com>...
> hkvats - I took the liberty of reviewing your code - hope you dont mind.|||This type of concatenation over rows is
very easy with the RAC utility for S2k.
No coding required.
Check out:
http://www.rac4sql.net/onlinehelp.asp?topic=236
RAC v2.2 and QALite released.
www.rac4sql.net