Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2014
    Posts
    1

    Unanswered: Concatenate Query

    Hi I would like a query which would return only unique Customer Numbers and concatenate the email addresses. I am using SSMS 2008.

    The table looks like below.

    Code:
    CUSTNMBR     Email_Recipient
    10811            accounts@blah.com
    11072            payables@blah.com
    10014            accounts@10014.com
    10014            receivables@test.com
    I would like the query to return like this

    Code:
    CUSTNMBR     Email_Recipient
    10811            accounts@blah.com
    11072            payables@blah.com
    10014            accounts@10014.com; receivables@test.com
    It doesn't really matter what order the addresses concatenate in, but there is a column called DEX_ROW_ID which could be used if an order is needed.

    Thanks, Tania

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Hi,

    Try something like this:

    Code:
    SELECT DISTINCT
        t.Customer, 
        STUFF( (SELECT '; ' + s.Email_Recipient
                FROM MyTable as s
                WHERE s.Customer = t.Customer
                FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
               ,1, 2, '') as Email_Recipient
    FROM MyTable as t
    Hope this helps.

  3. #3
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    This is a violation of First Normal Form (1NF) and we do not do this in SQL. This is a display issue for the presentation layers. That is where you use XML, a report writer, etc. No competent SQL programmer would do it in a query.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    To re-iterate what Joe Celko stated above:

    This is not something you should do in SQL. You should do this in your presentation layer (e.g. form, report, web page, etc).

    We store data in that original format for a reason

    However saying that "no competent programmer would do it in a query" is untrue and overly harsh. I would consider it appropriate practice in a one off, ad-hoc query but I wouldn't let something like this in to production.
    George
    Home | Blog

Posting Permissions

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