SELECT *FROM(select Val,Cat,ROW_NUMBER() over (partition by Cat ORDER BY Empid ) AS RnFROM (SELECT Empid,Empname,CAST(Empsalary as varchar(50)) AS EmpSalary,CAST(Empdesignation as varchar(50)) AS Empdesignation FROM Sample)sunpivot (Val FOR Cat IN ([EmpName],[EmpSalary],[EmpDesignation]))u)mPIVOT(MAX(Val) FOR Rn IN ([1],[2],[3],[4],[5],[6]))pORDER 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

could you please help me on making the below query to fetch top 5000 records. currently it only take 6 records.

SELECT *FROM(select Val,Cat,ROW_NUMBER() over (partition by Cat ORDER BY Empid ) AS RnFROM (SELECT Empid,Empname,CAST(Empsalary as varchar(50)) AS EmpSalary,CAST(Empdesignation as varchar(50)) AS Empdesignation FROM Sample)sunpivot (Val FOR Cat IN ([EmpName],[EmpSalary],[EmpDesignation]))u)mPIVOT(MAX(Val) FOR Rn IN ([1],[2],[3],[4],[5],[6]))pORDER BY CASE Cat WHEN 'Empname' THEN 1 WHEN 'salary' THEN 2 ELSE 3 END

could you please help me on making the below query to fetch top 5000 records. currently it only take 6 records.

SELECT *FROM(select Val,Cat,ROW_NUMBER() over (partition by Cat ORDER BY Empid ) AS RnFROM (SELECT Empid,Empname,CAST(Empsalary as varchar(50)) AS EmpSalary,CAST(Empdesignation as varchar(50)) AS Empdesignation FROM Sample)sunpivot (Val FOR Cat IN ([EmpName],[EmpSalary],[EmpDesignation]))u)mPIVOT(MAX(Val) FOR Rn IN ([1],[2],[3],[4],[5],[6]))pORDER BY CASE Cat WHEN 'Empname' THEN 1 WHEN 'salary' THEN 2 ELSE 3 END

Help me on making this be dynamic sql

Thanks

Are you sure you have read the article posted about limitations?

Columns per SELECT statement = 4,096

What is the purpose of what you are doing? This isn't usable in this format by anything other than a computer which should be able to handle the data in a standard format anyway.

If you are deadset on trying to force this you will have to first reduce the number of columns. Then you will need to use some dynamic sql. This is a twist on a dynamic cross tab. Take a look at the articles in my signature. They will help you get started.