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

Answer 1

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]