Hi Erikkur,

Thanks for the reply. Here is my try.

SELECT *

FROM

(

select Val,Cat,ROW_NUMBER() over (partition by Cat ORDER BY Empid ) AS Rn

FROM (SELECT Empid,Empname,CAST(Empsalary as varchar(50)) AS EmpSalary,CAST(Empdesignation as varchar(50)) AS Empdesignation FROM Sample)s

unpivot (Val FOR Cat IN ([EmpName],[EmpSalary],[EmpDesignation]))u

)m

PIVOT(MAX(Val) FOR Rn IN ([1],[2],[3],[4],[5],[6]))p

ORDER BY CASE Cat WHEN 'Empname' THEN 1 WHEN 'salary' THEN 2 ELSE 3 END

But i have half million records in my table and if i need to select Top 10000, i need to make this as dyncamic. Any help on making it to dynamic sql please