SQL Query Distinct Column Concatenate Other Column

Question

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

EmailAddress          Column2                                      
--------------------- ----------------
sean@abc.com          Value1                                     
sean@abc.com          Value2
test@abc.com          Value5 


What I really want to achieve are the following results:


EmailAddress          Column2                                      
--------------------- ------------------
sean@abc.com          Value1, Value2
test@abc.com          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.

score 1 · Accepted Answer · edited May 23 '17 at 12:18

1

Try the answer to this question

edited May 23 '17 at 12:18

Community

1
1

answered Jun 25 '09 at 11:39

Jeremy Smyth

23,270
2
52
65

score 0 · Answer 2 · answered Jun 25 '09 at 11:51

In SQL Server 2005+:

WITH    q AS
        (
        SELECT  'sean@abc.com' AS address, 'Value1' AS value
        UNION ALL
        SELECT  'sean@abc.com', 'Value2'
        UNION ALL
        SELECT  'test@abc.com', '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

score 0 · Answer 3 · edited May 23 '17 at 10:32

0

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

edited May 23 '17 at 10:32

Community

1
1

answered Jun 25 '09 at 11:58

van

74,297
13
168
171

SQL Query Distinct Column Concatenate Other Column

3 Answers3