SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table [User]( id varchar(10),
                   Name varchar(20))
                   
insert into [User] values('AAA','John'),('BBB','Mike')

create table Info (id varchar(10),
                   CONTANCT_INFO varchar(30),
                   CONTACT_DESC varchar(20))
                   
insert into Info values
('AAA','111111','Phone'),
('AAA','222222','Phone'),
('AAA','aaa@example.com','Email'),
('AAA','bbb@example.com','Email'),
('BBB','333333','Phone'),
('BBB','ccc@example.com','Email'),
('BBB','ddd@example.com','Email'),
('BBB','eee@example.com','Email'),
('BBB','444444','Phone'),
('BBB','555555','Phone')

;with PivotData as
(
select I.id,
       U.Name,
       I.CONTANCT_INFO,
       I.CONTACT_DESC + '_'+
       cast( row_number() over (partition by I.id,CONTACT_DESC 
                                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;

id	Name	Phone_1	Phone_2	Phone_3	Email_1	Email_2	Email_3
AAA	John	111111	222222	null	aaa@example.com	bbb@example.com	null
BBB	Mike	444444	555555	333333	ccc@example.com	ddd@example.com	eee@example.com