Posted by othellomy on 03/12/07 08:43

On Mar 9, 3:43 am, "Steve London" <sylon...@optonline.net> wrote:
> I'm sure this has been brought up many times, but I will ask anyway.
>
> Let's say I have 2 tables related:
>
> Owner:
> ---------
> o_id
> o_name
>
> Dog:
> ---------
> d_id
> d_name
> o_id - for Owner table.
>
> If the data is laid out as
>
> o_id o_name
> 1 John
>
> d_id d_name o_id
> 1 Skippy 1
> 2 Fido 1
>
> How can I make a query that will produce the following results:
>
> o_id o_name owned dog names
> 1 John Skippy, Fido
>
> I think it has something to do with unions but I can't seem to get it. I'm
> using SQL Server Compact Edition.

hi,
Following code will work but you might have to customize it to fit
your requirement.

declare @table table (o_id int, o_name varchar(50))
declare @dog table (d_id int, d_name varchar(50),o_id int)
declare @owner_dogs table(o_id int, o_name varchar(50), d_name
varchar(50))
insert @table values (1,'John')
insert @dog values(1,'Skippy',1)
insert @dog values(2,'Fido',1)

declare @dog_name varchar(50)

while (select count(*) from @dog) > 0
begin
set rowcount 1
if @dog_name is null
begin
select @dog_name = d_name
from @dog
where o_id = 1
end
else
select @dog_name = @dog_name + ', ' + d_name
from @dog
where o_id = 1

delete @dog
end

insert @owner_dogs values(1,'John',@dog_name)
select * from @owner_dogs
set rowcount 0

[Back to original message]