SQL Query Distinct Column Concatenate Other Column

Question

Hi All, I have a MS SQL query that joins multiple tables and an example of the results are:

EmailAddress          Column2                                      
--------------------- ----------------
[email protected]          Value1                                     
[email protected]          Value2
[email protected]          Value5 


What I really want to achieve are the following results:


EmailAddress          Column2                                      
--------------------- ------------------
[email protected]          Value1, Value2
[email protected]          Value5

There are other columns that are identical for each row with the same email address. Can anyone help me with the SQL to return distinct email addresses and concatenate the column2 information?

Thanks for any feedback.

Answer 1

Try the answer to this question

Answer 2

In SQL Server 2005+:

WITH    q AS
        (
        SELECT  '[email protected]' AS address, 'Value1' AS value
        UNION ALL
        SELECT  '[email protected]', 'Value2'
        UNION ALL
        SELECT  '[email protected]', 'Value5'
        )
SELECT  (
        SELECT  CASE WHEN ROW_NUMBER() OVER (ORDER BY value) > 1 THEN ', ' ELSE '' END + value
        FROM    q qi
        WHERE   qi.address = qo.address
        FOR XML PATH('')
        )
FROM    (
        SELECT  DISTINCT address
        FROM    q
        ) qo

Answer 3

Same answer from Jeremy, but I would use the other answer with XML PATH trick