Query Help

[Sammy]

Hi,

I need help with writing a query to group the following data:

Name Fruit
----------- ---------
John Smith Apples
John Smith Oranges
John Smith Bananas
Mike Jones Apples
Mike Jones Kiwi
Mike Jones Grapes
Mike Jones Pears


I need it to look like this:

Name Fruit
----------- ---------
John Smith Apples, Oranges, Bananas
Mike Jones Apples, Kiwi, Grapes, Pears

Can someone please help me with this?

Thanks so much!
- Sammy

[tim wilkinson]

This is how i'd probably do it - i'm not sure how fast it would be for bigger tables, though... Also unless you use SQL 7 you can't have a string of more than 255 chars.

/************************************************** ***********
This bit stops it from repeatedly printing '1 row(s) affected'
************************************************** ***********/

set nocount on

/******************************************
This bit is just to set up the sample table
******************************************/

if exists (select * from sysobjects where name = 'name_to_fruit' and type = 'u&#39
drop table name_to_fruit
go

create table name_to_fruit(name varchar(10), fruit varchar(10))
insert name_to_fruit values('John Smith', 'Apples&#39
insert name_to_fruit values('John Smith', 'Oranges&#39
insert name_to_fruit values('John Smith', 'Bananas&#39
insert name_to_fruit values('Mike Jones', 'Apples&#39
insert name_to_fruit values('Mike Jones', 'Kiwi&#39
insert name_to_fruit values('Mike Jones', 'Grapes&#39
insert name_to_fruit values('Mike Jones', 'Pears&#39
declare @letter int
select @letter = 32
while @letter < 97
begin
insert name_to_fruit values(&#39;Mike Jones&#39;, char(@letter))
select @letter = @letter +1
end


/************************************************** *************
This bit drops the temp table if it already exists.
I prefer to do this rather than drop it at the end of the batch
- that way you can have a look in it if anything goes wrong.
************************************************** *************/

if exists (select * from sysobjects where name = &#39;name_to_all_fruit&#39; and type = &#39;u&#39
drop table name_to_all_fruit
go

/************************************************** ****************
This bit cursors through the names and their fruit (nested cursor),
gets the results and puts &#39;em in a temp. table.
************************************************** ****************/

create table name_to_all_fruit(name varchar(10), allfruit varchar(255))
declare @name varchar(10), @fruit varchar(10), @allfruit varchar(255), @fetchname int, @fetchfruit int
declare name_cr cursor for
select distinct name
from name_to_fruit
open name_cr
fetch name_cr into @name
select @fetchname = @@fetch_status
while @fetchname = 0
begin
declare fruit_cr cursor for --try not redeclaring
select distinct fruit
from name_to_fruit
where name = @name
open fruit_cr
fetch fruit_cr into @fruit
select @fetchfruit = @@fetch_status
while @fetchfruit = 0
begin
select @allfruit = @allfruit + &#39;, &#39; + @fruit
fetch fruit_cr into @fruit
select @fetchfruit = @@fetch_status
end
close fruit_cr
deallocate fruit_cr
if len(@allfruit) > 2
select @allfruit = substring(@allfruit,3,len(@allfruit)-2)
insert name_to_all_fruit values(@name, @allfruit)
select @allfruit = null
fetch name_cr into @name
select @fetchname = @@fetch_status
end
close name_cr
deallocate name_cr


/******************************
This bit displays your results.
*******************************/

select * from name_to_fruit
select * from name_to_all_fruit

set nocount off


------------
Sammy at 6/30/00 6:10:34 PM


Hi,

I need help with writing a query to group the following data:

Name Fruit
----------- ---------
John Smith Apples
John Smith Oranges
John Smith Bananas
Mike Jones Apples
Mike Jones Kiwi
Mike Jones Grapes
Mike Jones Pears


I need it to look like this:

Name Fruit
----------- ---------
John Smith Apples, Oranges, Bananas
Mike Jones Apples, Kiwi, Grapes, Pears

Can someone please help me with this?

Thanks so much!
- Sammy