SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    Non-Member
    Join Date
    Jul 2008
    Posts
    221
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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.

  2. #2

  3. #3
    SitePoint Wizard webcosmo's Avatar
    Join Date
    Oct 2007
    Location
    Boston, MA
    Posts
    1,433
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    Non-Member
    Join Date
    Jul 2008
    Posts
    221
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  5. #5
    SitePoint Wizard webcosmo's Avatar
    Join Date
    Oct 2007
    Location
    Boston, MA
    Posts
    1,433
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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:
    cosmocentral.com/2010/10/select-comma-seperated-list-values-ms-sql/


    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •