Try this

declare @Tmp table(PROPOSER varchar(100),APPROVER varchar(100),DESIGNATION varchar(100))

insert into @Tmp

select 'kiran','ARUN','CFO' union all

select 'kiran','kranthi','HOD' union all

select 'kiran','PHANI','CEO' union all

select 'kiran','SANGEET','ACCOUNT' union all

select 'SURYA','MALATHI','ACCOUNT' union all

select 'SURYA','SRIKANTH','ACCOUNT' union all

select 'SWATHI','MALATHI','HOD' union all

select 'SWATHI','PANKAJ','CEO' union all

select 'SWATHI','VARSHA','CEO'

SELECT PROPOSER,[HOD],[CEO],[CFO],[ACCOUNT]

FROM

(select PROPOSER,DESIGNATION,(select APPROVER+',' as[text()] from @Tmp [1] where [1].PROPOSER=[2].PROPOSER and [1].DESIGNATION=[2].DESIGNATION for XML path('')) as APPROVER

from @Tmp [2])A

PIVOT

(MAX(APPROVER) FOR DESIGNATION IN([HOD],[CEO],[CFO],[ACCOUNT]))PVT

Output

PROPOSER [HOD] [CEO] [CFO] [ACCOUNT]

kirankranthi,PHANI,ARUN,SANGEET,

SURYANULLNULLNULLMALATHI,SRIKANTH,

SWATHIMALATHI,PANKAJ,VARSHA,NULLNULL