Concatenate distinct values from multiple rows and multiple columns

Question

I have this table:

VAT | Email1 | Email2
000 | [email protected] | [email protected]
000 | [email protected] | -
000 | [email protected] | [email protected]
000 |  -      | [email protected]

I want this result:

VAT | Emails
000 |  [email protected], [email protected], [email protected], [email protected]

How can I do this in SQL?

Note that I want to concatenate values from multiple columns and multiple rows simultaneously.

Select distinct t2.email1, substring((Select ', '+t1.email2 From #temp1 t1 Where T1.email1= T2.email1 ORDER BY T1.email1 For XML PATH ('')),2, 1000) email From #temp1 T2 — Daniel Marcus, Mar 21 '18 at 17:06
Perhaps have to do subquery concatenation for each field then concatenate those results. — June7, Mar 21 '18 at 19:44

score 4 · Answer 1 · answered Mar 22 '18 at 05:19

Well, it's not an exact duplicate of the question Lad2025 linked to, but the answers to that question does show how to convert values of different rows into a comma separated string.
The one thing you have left to do is to get a distinct list of emails per vat from both columns. Here is one way to do it:

First, Create and populate sample table (Please save us this step in your future questions):

DECLARE @T AS TABLE
(
    VAT char(3),
    Email1 char(6),
    Email2 char(6)
)

INSERT INTO @T(VAT,Email1, Email2) VALUES
('000', '[email protected]', '[email protected]'),
('000', '[email protected]', NULL),
('000', '[email protected]', '[email protected]'),
('000', NULL, '[email protected]')

Then, use a common table expression to combine values from email1 and email2 using union.
Note that union will remove duplicate values so you will get a distinct list of emails for each vat value:

;WITH CTE AS
(
    SELECT VAT, Email1 As Email
    FROM @T
    UNION
    SELECT VAT, Email2
    FROM @T
)

Then use for xml path to get a comma delimited list from the email column of the cte (that will ignore the null values), and stuff to remove the first comma:

SELECT DISTINCT VAT, 
                (
                    SELECT STUFF(
                        (SELECT ',' + Email
                        FROM CTE t1
                        WHERE t0.VAT = t1.VAT
                        FOR XML PATH(''))
                        , 1, 1, '')
                ) As Emails
FROM CTE t0

Results:

VAT     Emails
000     [email protected],[email protected],[email protected],[email protected]

score 0 · Answer 2 · answered Mar 22 '18 at 21:58

Here's another option, but the above might be faster.

DECLARE @TBL TABLE(VAT varchar(10), Email1 varchar(50), Email2 varchar(50))
INSERT INTO @TBL select '000','[email protected]','[email protected]'
INSERT INTO @TBL  select '000','[email protected]',''
INSERT INTO @TBL  select '000','[email protected]','[email protected]'
INSERT INTO @TBL select '000','[email protected]','[email protected]'
INSERT INTO @TBL select '001','[email protected]','[email protected]'
INSERT INTO @TBL select '001','[email protected]','[email protected]'
INSERT INTO @TBL select '001','[email protected]','[email protected]'
INSERT INTO @TBL select '001','[email protected]','[email protected]'
INSERT INTO @TBL select '001',NULL,'[email protected]'

SELECT VAT, '' + REVERSE(STUFF(REVERSE(( select x.Email + ','
FROM (
    select VAT, Email1 as Email 
    from @TBL T2 
    WHERE T2.VAT = T1.VAT
        AND ISNULL(Email1,'') > ''
    GROUP BY VAT, EMAIL1
    union 
    select VAT, Email2 as Email 
    from @TBL T3 
    WHERE T3.VAT = T1.VAT
        AND ISNULL(Email2,'') > ''
    GROUP BY VAT, EMAIL2
) x
FOR XML PATH('')
)), 1, 1, '' ) ) + '' as Email
from @TBL T1
GROUP by T1.VAT

Results:

VAT | Email
000 | [email protected],[email protected],[email protected],[email protected],[email protected]
001 | [email protected],[email protected],[email protected],[email protected],[email protected],[email protected]

Concatenate distinct values from multiple rows and multiple columns

2 Answers2