Please help me with the below mentioned Scenario->
I have table named as cricket wherein columns are (cric_id int identity,nation varchar(20),Format varchar(20),Rank int)
Data is ->
Now I need to generate a Report in ASP.NET C# where i need a data in format given below
Nation,Rank,IPL,ODI,TEST,T20... and so on I mean i need to dynamically generate a report by transposing rows of column format.So If I add a new value in format column , It should be displayed as a column in my Report
Immediate response will be much appreciatedThanks in Advance
I beleive this could be handled by Dynamic query instead of CURSORs.
DROP TABLE [#cricket]
In SQL have many way's to achieve your requirement e.g. CASE statement, PIVOT etc.
For your requirement PIVOT will be the better choice if you have SQL Server 2005 or 2008.
answered Jan 16, 2012 at 09:40 PM
I'd say this is a task for PIVOT, no need for dynamic sql or cursors.
The "fishy" part is that pivot needs an aggregate function and you have scalar values that you want to display. But from what I can see, the values are unique, so using MAX or another aggregate function on the scalar values won't make a difference.
I assume that the different formats are known beforehand. If they are not, dynamic SQL will be the thing to use:
Still no need for cursors.
answered Jan 17, 2012 at 01:06 AM