Sql group by - need help

Hello,

I have a query like this:

ID Date Email

2 5/19/2011 1@aol.com
2 5/19/2011 2@aol.com
3 5/19/2011 3@aol.com
4 5/19/2011 4@aol.com
4 5/19/2011 5@aol.com

How do I select the combined email(s) with any record that is the same ID and Date.

I tried this:

Select ID, Date, Email
From qryName
Group by ID, Date, Email

I does not work.

The output I like to see is:

ID Date Email
2 5/19/2011 1@aol.com; 2@aol.com
3 5/19/2011 3@aol.com
4 5/19/2011 4@aol.com; 5@aol.com

Thanks very much.

MySQL :: MySQL 5.5 Reference Manual :: 11.16.1 GROUP BY (Aggregate) Functions

probably something like this

Select ID, Date, Emails=dbo.CommaSeperatedEmails(Id)
From qryName
Where Id In
(
Select ID
From qryName
Group by ID, Date
)

You will probably need a function for the CommaSeperatedEmails.

I don’t know how to write a function for the CommaSeperatedEmails.

What is that about?

I tried this store procedure:

DECLARE @emails VARCHAR(1024)

SELECT
@emails= COALESCE(@emails+ ‘,’, ‘’) + emails
FROM qryName

SELECT emails= @emails
GO

But it does not work.

Can you help?

Thanks.

I am not sure which database you using; looks like MS Sql.
Here is an example code in MS SQL you can use for a Scalar Function:

reference:

Sql Create Script:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[GetCommaSeperatedEmails]
(
– Add the parameters for the function here
@Id int
)
RETURNS varchar(max)
AS
BEGIN

declare @emails varchar(max)

Select @emails=COALESCE(@emails+', ','')+email from qryName Where Id=@Id

-- Return the result of the function
RETURN @emails

END

GO