Pivot table with multiple aggregate value

I am trying to pivot below table, is that possible?

But if I aggregate them by Code, Value A or B will be gone because of it.

From

Name	Code	Value
Kevin	Code1	A
Kevin	Code1	B
Kevin	Code2	C
Kevin	Code3	D
Tom	Code1	E
Tom	Code2	F
Tom	Code3	G

To

Name	Code1	Code2	Code3
Kevin	A	C	D
Kevin	B	C	D
Tom	E	F	G

Query I tried….

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

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.Code) 
FROM Table c
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(max)'),1,1,'')

set @query = 'with mapping as 
             (SELECT 
                Name
                , ' + @cols + ' 
             from (select name
                       , Code
                       , Value
                    from Table) x
             pivot 
             (max(Code)
             for Name in (' + @cols + ')) p )' 

execute(@query)

Tried myself but I can only achieve below

Name	Code1	Code2	Code3
Kevin	A or B(depend on aggregated max or min)	C	D
Tom	E	F	G

Thanks in advance

Answer

This is a fixed column list version. You should build your dynamic one using this template.

select Name 
  , first_value(Code1) over(partition by Name order by rn) Code1
  , first_value(Code2) over(partition by Name order by rn) Code2
  , first_value(Code3) over(partition by Name order by rn) Code3
from (
   select t.* , row_number() over(partition by Name, Code order by Value) rn
   from tbl t
   ) t
pivot (max(value) for code in (Code1, Code2, Code3)) p
order by Name;