Grouping Contact details into separate columns in SQL based on its type

Question

I have multiple phone number, multiple email address, multiple fax address in a contact column. I need a query to get the result like below

Contact Id |Phone                 |Email                       |Fax
1          |Phone1, Phone2, Phone3|Email1, Email2,Email3,Email4|Fax1, Fax2
2          |Phone1, Phone2        |Email1, Email2,Email3       |Fax1, Fax2

My Input is like

Value    MeansOfCommunicationDescription    ContactId
email1@port2.com    Email    2
www.1_port2.com    Web Site    2
Test Insert    Fax    2
Test Insert    Web Site    2
Test Insert    Web Site    2
Test Insert    Web Site    2
Test Insert    Web Site    2
Test Insert    Web Site    2
Test Insert    Web Site    2
test     Phone    2
test     Phone    2
test     Phone    2
Test Insert    Web Site    2
Test Insert    Email    2

Barbaros Özhan · Answer 1 · 2019-08-02T09:11:05.957

1

if your DB's version is 2017+,you can use string_agg() function

select ContactId,
       string_agg(phone,',') within group (order by phone) as phone,
       string_agg(email,',') within group (order by email) as email,
       string_agg(fax,',') within group (order by fax) as fax
  from tab
 group by ContactId

edited Aug 02 '19 at 09:11

answered Aug 02 '19 at 08:58

Barbaros Özhan

59,113
10
31
55

score 1 · Accepted Answer · answered Aug 02 '19 at 09:26

You can use CTE to get your desired results. You can try below query:

;WITH ctePhone
    AS (
        SELECT DISTINCT ContactId           [ID],
        STUFF(( SELECT N', ' + Phone
            FROM Table1 B
             WHERE B.ContactId = A.ContactId
                FOR XML PATH(''), TYPE
                    ).value('(.)', 'NVARCHAR(MAX)'), 1, 1, '') [Phone],
        STUFF(( SELECT N', ' + Email
            FROM Table1 B
             WHERE B.ContactId = A.ContactId
                FOR XML PATH(''), TYPE
                    ).value('(.)', 'NVARCHAR(MAX)'), 1, 1, '') [Email],
        STUFF(( SELECT N', ' + Fax
            FROM Table1 B
             WHERE B.ContactId = A.ContactId
                FOR XML PATH(''), TYPE
                    ).value('(.)', 'NVARCHAR(MAX)'), 1, 1, '') [Fax]
            FROM Table1 A
    )
SELECT DISTINCT #Table1.ContactId,
    ctePhone.Phone,
    ctePhone.Email,
    ctePhone.Fax
FROM Table1
    INNER JOIN ctePhone
        ON Table1.ContactId = ctePhone.[ID]

Grouping Contact details into separate columns in SQL based on its type

2 Answers2