Concatenate distinct values from multiple rows and multiple columns

Question

I have this table:

VAT | Email1 | Email2
000 | a@a.it | b@b.it
000 | a@a.it | -
000 | a@a.it | c@c.it
000 |  -      | d@d.it

I want this result:

VAT | Emails
000 |  a@a.it, b@b.it, c@c.it, d@d.it

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', 'a@a.it', 'b@b.it'),
('000', 'a@a.it', NULL),
('000', 'a@a.it', 'c@c.it'),
('000', NULL, 'd@d.it')

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     a@a.it,b@b.it,c@c.it,d@d.it

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','a@a.it','b@b.it'
INSERT INTO @TBL  select '000','a@a.it',''
INSERT INTO @TBL  select '000','a@a.it','c@c.it'
INSERT INTO @TBL select '000','pizz@pizza.com','d@d.it'
INSERT INTO @TBL select '001','slice@pizza.com','d@d.it'
INSERT INTO @TBL select '001','pizz@pizza.com','q@q.it'
INSERT INTO @TBL select '001','cheese@pizza.com','s@s.it'
INSERT INTO @TBL select '001','slice@pizza.com','s@s.it'
INSERT INTO @TBL select '001',NULL,'s@s.it'

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 | a@a.it,b@b.it,c@c.it,d@d.it,pizz@pizza.com
001 | cheese@pizza.com,d@d.it,pizz@pizza.com,q@q.it,s@s.it,slice@pizza.com

Concatenate distinct values from multiple rows and multiple columns

2 Answers2