To clean up the presence of trailing commas, use STUFF on ssurve's code like the following...

SELECT PROPOSER,HOD,CEO,CFO,ACCOUNT

FROM ( --=== This concatenates multiple Approver values.

SELECT PROPOSER,

DESIGNATION,

APPROVER = STUFF((SELECT ',' + APPROVER

FROM @Tmp t1

WHERE t1.PROPOSER = t2.PROPOSER

AND t1.DESIGNATION = t2.DESIGNATION

ORDER BY t1.PROPOSER

FOR XML PATH('')),1,1,'')

FROM @Tmp t2

) d

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

;

Output:

PROPOSER HOD CEO CFO ACCOUNT

-------- ------- ------------- ---- ----------------

kiran kranthi PHANI ARUN SANGEET

SURYA NULL NULL NULL MALATHI,SRIKANTH

SWATHI MALATHI PANKAJ,VARSHA NULL NULL