Alternative to Cursor - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Alternative to Cursor

Author

Topic

cardullo4321
Starting Member

40 Posts

Posted - 2013-05-31 : 06:03:19

Is there any alternative to cursors that will allow me to get the output needed from the table structure? I heard cursors should be avoided at pretty much all cost.

Table Structure
Last First Case Amount
Smith Greg 01 100
Doe Joe 02 200

Output
1 Smith
2 Greg
3 01
4 100
5 Doe
6 Joe
7 02
8 200

Gregory Cardullo

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-05-31 : 06:36:07

That's a kind of formatting to display or report the data.
SQL isn't really made for that.
If you have the chance then do it in your front end.

Too old to Rock'n'Roll too young to die.

cardullo4321
Starting Member

40 Posts

Posted - 2013-05-31 : 07:50:43

I agree with you. There is actually more data to that needs to be output but I just wanted to display an example. I am loading the data from two excel spreadsheets into separate tables through SSIS. It is joining the two reports but also joining data from another SQL table. Then the output data is getting loaded into another table.

If cursor is the best way, then I am happy to hear that I finished the projects basically. However, I just want to learn the most efficient ways to do things in SQL.

Gregory Cardullo

robvolk
Most Valuable Yak

15732 Posts

Posted - 2013-05-31 : 09:31:25

A cursor is never the best way:

-- setup
DECLARE @t TABLE(LAST VARCHAR(30), FIRST VARCHAR(30), [CASE] VARCHAR(30), Amount VARCHAR(30))
INSERT @t VALUES('Smith', 'Greg', '01', '100')
INSERT @t VALUES('Doe', 'Joe', '02', '200')

-- query
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) #, val 
FROM @t a
UNPIVOT(val FOR col IN ([LAST],[FIRST],[CASE],[Amount])) b

Note that you cannot guarantee order without an ORDER BY clause, so it's possible that the values could be sorted incorrectly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources