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

innovator gravatar image

innovator
335 15 15 17

(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

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

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

Amardeep gravatar image

Amardeep
1.3k 87 88 89

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

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.6k 17 20 33

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][1]

[1]: http://www.sqlservercentral.com/articles/T-SQL/63681/
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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x415

asked: Jan 16, 2012 at 07:51 AM

Seen: 1267 times

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