Reputation: 1918
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
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
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