Dynamic table creation using Cursor

Question

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

Answer 1

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]

Answer 2

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.

Answer 3

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

Dynamic table creation using Cursor

Follow this question

Related Questions