How to merge many rows with the same ID with SQL Server

I need the help from the community to solve the following problem that I found (I’m using SQL Server):

I have two tables, USER and INFO.

USER:

ID    NAME
----------
AAA   John
BBB   Mike

INFO:

ID      CONTANCT_INFO    CONTACT_DESC
-------------------------------------
AAA        111111           Phone
AAA        222222           Mobile
AAA    [e-mail]      Email
AAA    [e-mail]      Email
BBB        333333           Phone
BBB    [e-mail]      Email
BBB    [e-mail]      Email
BBB    [e-mail]      Email
BBB        444444           Phone
BBB        555555           Mobile

I want to merge the info table per user in one row as follows:

ID  NAME    Phone1  Phone2  Phone3  Email1          Email2          Email3
---------------------------------------------------------------------------
AAA John    111111  222222          [e-mail] [e-mail] 
BBB Mike    333333  444444  555555  [e-mail] [e-mail] [e-mail]

I’ve been trying many options and also surfing through stackoverflow posts, but without any results to my situation.

Thanks in advance!

Kind regards,

Aovon.

Answer

If you are sure that there will be only 3 phones and emails then following query will work:

;with PivotData as
(
select I.id,
       U.Name,
       I.CONTANCT_INFO,
       Replace(I.CONTACT_DESC,'Mobile','Phone') + '_'+
       cast( row_number() over (partition by I.id,
                                Replace(I.CONTACT_DESC,'Mobile','Phone') 
                                order by (Select null))--change here for sort order
       as char(1)) as CONTACT_DESC_Attribute
From Info I 
join [User] U on U.id = I.Id

)
SELECT id,Name,[Phone_1],[Phone_2],[Phone_3],
       [Email_1],[Email_2],[Email_3]
FROM PivotData
  PIVOT( Max(CONTANCT_INFO)
    FOR CONTACT_DESC_Attribute IN ([Phone_1],[Phone_2],[Phone_3],
                                   [Email_1],[Email_2],[Email_3]) ) AS P;

Sample code here..

Use dynamic query for pivoting as:

 DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' 
                   + QUOTENAME(Replace(I.CONTACT_DESC,'Mobile','Phone') + '_'+
                               cast( row_number() over (partition by I.id,
                                Replace(I.CONTACT_DESC,'Mobile','Phone') 
                                order by (Select null))--change here for sort order
                      as char(1))) 
            FROM Info I
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT id,Name, ' + @cols + ' from 
            (
               select I.id,
                      U.Name,
                      I.CONTANCT_INFO,
                      Replace(I.CONTACT_DESC,''Mobile'',''Phone'') + ''_''+
                      cast( row_number() over (partition by I.id,
                                Replace(I.CONTACT_DESC,''Mobile'',''Phone'') 
                                order by (Select null))--change here for sort order
                      as char(1)) as CONTACT_DESC_Attribute
                From Info I 
                join [User] U on U.id = I.Id

           ) x
            pivot 
            (
                 max(CONTANCT_INFO)
                for CONTACT_DESC_Attribute in (' + @cols + ')
            ) p '
            
execute(@query); 

Sample code here..

Hope this helps!!!