loops,ROW_NUMBER,cursor

Question

loops,ROW_NUMBER,cursor

peleg

SSCrazy

Points: 2611
More actions
March 17, 2010 at 3:16 am

#220001

i have a query like this:
SELECT StartDate,R1,SUM(Total),ROW_NUMBER() OVER(ORDER BY StartDate ) AS 'id'
FROM [MonthlySummary] (NOLOCK)
Group by [StartDate],R1
Order by StartDate,R1
is there a way to loop through these result, withought using cursor, and based on the id value i get from the ROW_NUMBER?
Thanks
Peleg

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150468 More actions · Answer 1

peleg k (3/17/2010)
i have a query like this:
SELECT StartDate,R1,SUM(Total),ROW_NUMBER() OVER(ORDER BY StartDate ) AS 'id'
FROM [MonthlySummary] (NOLOCK)
Group by [StartDate],R1
Order by StartDate,R1
is there a way to loop through these result, withought using cursor, and based on the id value i get from the ROW_NUMBER?

Peleg,

You can use a variable and a WHILE loop. Not really any better than using a cursor, though.

If you can say more about why you need the loop, it might help suggest an alternate solution.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

peleg SSCrazy Points: 2611 More actions · Answer 2

i want to build a table which based on the StartDate, where i will have

StartDate(1),StartDate(2).........,R1,total

and this table at the end i will wriite to a file.

Paul White SSC Guru Points: 150468 More actions · Answer 3

Ok. Some example data and expected output would help.

Otherwise, I'd just say use an INSERT statement straight from your SELECT.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

peleg SSCrazy Points: 2611 More actions · Answer 4

this is how the table will look: (the total value will be add to the column with the date)

20100101000000,20100301000000,.......,R1

130,0,,,..., ,'a'

10,330,,6,..., ,'b'

Paul White SSC Guru Points: 150468 More actions · Answer 5

Perhaps someone else will have the patience to extract some workable data and requirements from you.

Good luck!

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004471 More actions · Answer 6

Peleg... for better answers quicker, please see the first link in my signature line below.

I see what you're trying to do... it's called "Concatenation". There's also several other ways to do this. Lookup BCP in Book Online for a method to export such data.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)