September 24, 2004 at 8:47 am
I have a table with data that looks like this:
| COL1 | COL2 | CATEGORY |
| 1 | 1a | A |
| 2 | 1b | A |
| 3 | 2 | A |
| 4 | 3 | A |
| 5 | 1 | B |
| 6 | 2 | B |
| 7 | 3a | B |
| 8 | 3b | B |
and I need to transform it to:
| sequence | COL2 | CATEGORY |
| 1 | 1a | A |
| 2 | 1b | A |
| 3 | 2 | A |
| 4 | 3 | A |
| 1 | 1 | B |
| 2 | 2 | B |
| 3 | 3a | B |
| 4 | 3b | B |
So the question is, with using cursors, how can I get a result set that includes sequential numbering that starts over with each new CATEGORY? I can do it using a cursor based solution, but I hate to use cursors.
Thanks,
Gordon
Gordon Pollokoff
"Wile E. is my reality, Bugs Bunny is my goal" - Chuck Jones
September 24, 2004 at 10:20 am
I think this should get it. It isn't the most ideal approach, but it should work.
update table set A.sequence = ( Select COUNT(*) from table B where B.category = A.category and B.sequence <= A.sequence) FROM table A
If you can assume that categories will be sequence as in your example you could do a TOP 1 Sequence in the subselect instead of COUNT(*) and then run subtraction agaist the returned number and the current one. The updates may get in the way of that process, though. You could improve the performance and reliability of that process using a variable based temp table. If your categories are grouped together as in your example and you want the whole temp table thing explained just say so.
September 24, 2004 at 11:33 am
Aaron,
Thanks for the reply. I had already solved the problem though it was good to see that my solution was essentially the same as yours.
Gordon Pollokoff
"Wile E. is my reality, Bugs Bunny is my goal" - Chuck Jones
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply