KGJ-Dev (5/6/2014)
Hi Chris,Thanks for your reply.
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 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
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.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/