grouping with multiple columns
-
Hi, Can someone please help me out!
I have a table which has 3 columns (name, age, food)
the data is as follows
Jane 12 peas
tony 12 bacon
sady 15 peas on toast
sady 15 bacon
how can I perform a select statement so that I get the following result?
Jane 12 peas
tony 12 bacon
sady 15 peas on toast,bacon
I just need the name and age to appear once and then the food to be separated by a comma or semi-colon if there are more than 1 food types
any help will be highly appreciated
-
Ok, I think the following code is the solution you want. However, please check the performance.
Create the following Select command
SELECT Name, Age, dbo.udfConcatRecFieldValues(Name, Age)
FROM aisTable1
GROUP BY Name, Age
dbo.udfConcatRecFieldValues : is a UDF (User Defind Function)
CREATE FUNCTION [dbo].[udfConcatRecFieldValues]
(@Name varchar(50), @Age int)
RETURNS varchar(4000)
AS
BEGIN
Declare @AllFood varchar(4000), @food varchar(50)
Select @AllFood = '',
@Food = ''
Declare aisTable1_Cursor Cursor Static ForWard_Only For
Select Food
From aisTable1 with(nolock)
Where Name = @Name and Age = @Age
Open aisTable1_Cursor
Fetch Next From aisTable1_Cursor Into @food
While @@Fetch_Status = 0
Begin
if (Len(LTrim(Rtrim(@AllFood))) = 0)
Select @AllFood = @food
Else
Select @AllFood = @AllFood + ', ' + @food
Fetch Next From aisTable1_Cursor Into @food
End
Close aisTable1_Cursor
Deallocate aisTable1_Cursor
Return ( @AllFood )
END
-
I would alter the UDF slightly to avoid the cursor:
SELECT @AllFood = Food + ',' + @AllFood
From aisTable1 with(nolock)
Where Name = @Name and Age = @Age
RETURN LEFT(@AllFood,LEN(@AllFood)-1)
-
Cliff you are great I was doing it using curssors !!!
Hold on hold on soldier
When you add it all up
The tears and marrowbone
There's an ounce of gold
And an ounce of pride in each ledger
And the Germans killed the Jews
And the Jews killed the Arabs
And Arabs killed the hostages
And that is the news
And is it any wonder
That the monkey's confused -
Thank you so much for your help. It worked perfectly. I used the cursor one and it worked great! I however didn't understand how to do it without the cursor. Anyhow, thanks a lot
Nneka
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply