Martina
Martina

Reputation: 1918

Concatenate distinct values from multiple rows and multiple columns

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.

Upvotes: 2

Views: 2124

Answers (2)

Matthew Weir
Matthew Weir

Reputation: 194

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]

Upvotes: 0

Zohar Peled
Zohar Peled

Reputation: 82474

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]

Upvotes: 4

Related Questions