x

Dynamic table creation using Cursor

Hi All,

Please help me with the below mentioned Scenario->

Scenario->

I have table named as cricket wherein columns are (cric_id int identity,nation varchar(20),Format varchar(20),Rank int)

Data is ->

 Cricid Nation Format Rank
 1    India    IPL    1
 2    India    ODI    2
 3    India    TEST    3
 4    India    T20    6
 5    Australia    IPL    9
 6    Australia    ODI    1
 7    Australia    TEST    2
 8    Australia    T20    3
 9    England    IPL    8
 10    England    ODI    7
 11    England    TEST    1
 12    England    T20    1
 13    South_Africa    IPL    6
 14    South_Africa    ODI    3
 15    South_Africa    TEST    2
 16    South_Africa    T20    5
 17    Sri_Lanka    IPL    4
 18    Sri_Lanka    ODI    6
 19    Sri_Lanka    TEST    5
 20    Sri_Lanka    T20    3
 21    bermuda    IPL    4

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 appreciated

Thanks in Advance

more ▼

asked Jan 16, 2012 at 07:51 AM in Default

avatar image

innovator
335 15 15 20

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

I beleive this could be handled by Dynamic query instead of CURSORs.

 CREATE TABLE #cricket 
 (cric_id int identity,nation varchar(20),Format varchar(20),Rank int)
 
 INSERT [#cricket]
         (
         [nation]
         ,[Format]
         ,[Rank]
         )
 SELECT 'India','IPL',1
 UNION ALL SELECT 'India','ODI',2
 UNION ALL SELECT 'India','TEST',3
 UNION ALL SELECT 'India','T20',6
 UNION ALL SELECT 'Australia','IPL',9
 UNION ALL SELECT 'Australia','ODI',1
 UNION ALL SELECT 'Australia','TEST',2
 UNION ALL SELECT 'Australia','T20',3
 UNION ALL SELECT 'England','IPL',8
 UNION ALL SELECT 'England','ODI',7
 UNION ALL SELECT 'England','TEST',1
 UNION ALL SELECT 'England','T20',1
 UNION ALL SELECT 'South_Africa','IPL',6
 UNION ALL SELECT 'South_Africa','ODI',3
 UNION ALL SELECT 'South_Africa','TEST',2
 UNION ALL SELECT 'South_Africa','T20',5
 UNION ALL SELECT 'Sri_Lanka','IPL',4
 UNION ALL SELECT 'Sri_Lanka','ODI',6
 UNION ALL SELECT 'Sri_Lanka','TEST',5
 UNION ALL SELECT 'Sri_Lanka','T20',3    
 UNION ALL SELECT 'bermuda','IPL',4
 SET NOCOUNT ON;
 
 DECLARE @columnNames NVARCHAR(MAX)

 SELECT  @columnNames = COALESCE(@columnNames + ',','') + '
 MAX(CASE [Format] WHEN '+ QUOTENAME([Format],'''') + ' THEN [Rank] ELSE NULL END) AS ' + [Format]
 FROM [#cricket]
 GROUP BY   [Format]
 SET @columnNames = 'SELECT nation, ' + @columnNames + ' FROM [#cricket]
 group by nation'
 
 --PRINT @columnNames
 
 EXEC sp_executesql @columnNames
     
 DROP TABLE [#cricket]
more ▼

answered Jan 16, 2012 at 10:33 PM

avatar image

Usman Butt
14k 6 13 21

Thnx usman!!

Jan 17, 2012 at 12:54 AM innovator

@Navin19 Did you award me the points intentionally? These points are actually deducted from your own pool, and transferred to me. If you wanted to mark my answer as accepted, then you should have clicked the tick sign :)

Jan 17, 2012 at 01:56 AM Usman Butt
(comments are locked)
10|1200 characters needed characters left

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.

DECLARE Cur CURSOR FOR
SELECT DISTINCT Format FROM Cricket
DECLARE @Temp NVARCHAR(MAX),
@AllFormat NVARCHAR(MAX),
@NationQuery NVARCHAR(MAX)

SET @AllFormat = ''
OPEN Cur
FETCH NEXT FROM Cur INTO @Temp
WHILE @@FETCH_STATUS = 0
BEGIN
SET @AllFormat = @AllFormat + '[' + @Temp + '],'
FETCH NEXT FROM Cur INTO @Temp
END
CLOSE Cur
DEALLOCATE Cur

SET @AllFormat = SUBSTRING(@AllFormat, 0, LEN(@AllFormat))

  • Building the pivot query

    SET @NationQuery = 'SELECT Nation, ' + @AllFormat + ' FROM (SELECT Nation,Rank, Format FROM Cricket) S PIVOT (SUM (Rank) FOR Format IN (' +@AllFormat + ')) AS pvt'

    EXEC sp_executesql @NationQuery

more ▼

answered Jan 16, 2012 at 09:40 PM

avatar image

Sharma
1.3k 88 91 95

Thnx amardeep!

Jan 17, 2012 at 12:54 AM innovator
(comments are locked)
10|1200 characters needed characters left

I'd say this is a task for PIVOT, no need for dynamic sql or cursors.

 CREATE TABLE #cricket 
 (cric_id int identity,nation varchar(20),Format varchar(20),Rank int)
 
 INSERT [#cricket]
         (
         [nation]
         ,[Format]
         ,[Rank]
         )
 SELECT 'India','IPL',1
 UNION ALL SELECT 'India','ODI',2
 UNION ALL SELECT 'India','TEST',3
 UNION ALL SELECT 'India','T20',6
 UNION ALL SELECT 'Australia','IPL',9
 UNION ALL SELECT 'Australia','ODI',1
 UNION ALL SELECT 'Australia','TEST',2
 UNION ALL SELECT 'Australia','T20',3
 UNION ALL SELECT 'England','IPL',8
 UNION ALL SELECT 'England','ODI',7
 UNION ALL SELECT 'England','TEST',1
 UNION ALL SELECT 'England','T20',1
 UNION ALL SELECT 'South_Africa','IPL',6
 UNION ALL SELECT 'South_Africa','ODI',3
 UNION ALL SELECT 'South_Africa','TEST',2
 UNION ALL SELECT 'South_Africa','T20',5
 UNION ALL SELECT 'Sri_Lanka','IPL',4
 UNION ALL SELECT 'Sri_Lanka','ODI',6
 UNION ALL SELECT 'Sri_Lanka','TEST',5
 UNION ALL SELECT 'Sri_Lanka','T20',3    
 UNION ALL SELECT 'bermuda','IPL',4
 SET NOCOUNT ON;
 
 WITH CTE AS(SELECT nation, [rank], [format]
 FROM #cricket )
 
 SELECT * FROM CTE 
 PIVOT(
     max(rank)
     FOR [Format] IN ([IPL],[ODI],[T20],[TEST])
 ) AS p

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:

 SET CONCAT_NULL_YIELDS_NULL ON;
 declare @formats nvarchar(max);
 WITH CTE AS(
  SELECT DISTINCT [format] from #cricket
 )
 SELECT @formats = COALESCE(@formats + ',','') + quotename([format]) from CTE;
 DECLARE @sql nvarchar(max)
 SET @sql = 'WITH CTE AS (SELECT nation, [rank], [format]
 FROM #cricket
 )
 SELECT * FROM CTE
 PIVOT(
    max([rank])
    FOR [Format] IN (' + @formats + ')
 ) As p
 '
 exec sp_executesql  @sql;

Still no need for cursors.

more ▼

answered Jan 17, 2012 at 01:06 AM

avatar image

Magnus Ahlkvist
21.1k 19 39 42

I thought of hard-coding IPL, TEST etc as well, but then thought it is a sample data and in future may be a new format could be added as well. So, went for the dynamic query. As far as PIVOT and AGGREGATED CASE are concerned, please have a look at this

Jan 17, 2012 at 01:14 AM Usman Butt

ok! Thnkx for the comment!

Jan 17, 2012 at 01:28 AM innovator
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x455

asked: Jan 16, 2012 at 07:51 AM

Seen: 1670 times

Last Updated: Jan 16, 2012 at 08:38 AM

Copyright 2016 Redgate Software. Privacy Policy