Posted by othellomy on 03/12/07 12:26

On Mar 12, 2:43 pm, othell...@yahoo.com wrote:
> 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- Hide quoted text -
>
> - Show quoted text -

Hi,
I just read your complete post and realized that you might not be able
to get it working. So here is the complete code:

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))

declare @dog_temp table (d_id int, d_name varchar(50),o_id int)
declare @id table (o_id int)

insert @table values (1,'John')
insert @table values (2,'Mary')

insert @dog values(1,'Skippy',1)
insert @dog values(2,'Fido',1)
insert @dog values(3,'Ralph',2)
insert @dog values(4,'Alf',2)

declare @dog_name varchar(50)
declare @o_id int

insert @id
select distinct o_id
from @table

select @o_id = max(o_id) from @id
--select top 1 @o_id = o_id from @id

while @o_id is not null
begin
delete @id
where o_id = @o_id

insert @dog_temp
select *
from @dog
where o_id = @o_id

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

delete @dog_temp
end

insert @owner_dogs (o_id,d_name)
values (@o_id,@dog_name)

set rowcount 0

select @o_id = max(o_id) from @id
end


update @owner_dogs
set o_name = a.o_name
from @table a,
@owner_dogs b
where b.o_id = a.o_id

select * from @owner_dogs

[Back to original message]