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