August 20, 2008 at 9:33 am
Hi Forum,
I have to convert data from our current system to the new one. One of my issues is that on the old system part of the data is stored horizontally while in the new system it's vertical.
Each line has a sequence number but when pivoting the data I need to resequence it to be compatible with the new system.
I loaded a temp table with my data to make it vertical, before I push this the table in the new system I need to set the OpenSeq value. It has to begin at 1, incrementing by 1 but restarting at 1 at each new ProdNum
Data as it is now.
Prodnum Style Color Label dimension Size OpenSeq
----------- --------- ---------- -------- --------- ----- -----------
21272 M0953015T 95308 SR 00 42 0
21272 M0953015T 95308 SR 00 44 0
21272 M0953015T 95308 SR 00 46 0
21272 M0953015T 95309 SR 00 42 0
21272 M0953015T 95309 SR 00 44 0
21272 M0953015T 95309 SR 00 46 0
21272 M0953015T 95352 SR 00 42 0
21272 M0953015T 95352 SR 00 44 0
21272 M0953015T 95352 SR 00 46 0
21272 M0953015T 95388 SR 00 42 0
21272 M0953015T 95388 SR 00 44 0
21272 M0953015T 95388 SR 00 46 0
31903 M17661092 001 SI 30 30 0
31903 M17661092 001 SI 30 32 0
31903 M17661092 001 SI 30 34 0
31903 M17661092 001 SI 30 36 0
31903 M17661092 001 SI 30 38 0
31903 M17661092 001 SI 30 40 0
31903 M17661092 001 SI 32 30 0
31903 M17661092 001 SI 32 32 0
31903 M17661092 001 SI 32 34 0
31903 M17661092 001 SI 32 36 0
31903 M17661092 001 SI 32 38 0
31903 M17661092 001 SI 32 40 0
31903 M17661092 001 SI 34 32 0
31903 M17661092 001 SI 34 34 0
31903 M17661092 001 SI 34 36 0
31903 M17661092 001 SI 34 38 0
Data as I need it after.
Prodnum Style Color Label dimension Size OpenSeq
----------- --------- ---------- -------- --------- ----- -----------
21272 M0953015T 95308 SR 00 42 1
21272 M0953015T 95308 SR 00 44 2
21272 M0953015T 95308 SR 00 46 3
21272 M0953015T 95309 SR 00 42 4
21272 M0953015T 95309 SR 00 44 5
21272 M0953015T 95309 SR 00 46 6
21272 M0953015T 95352 SR 00 42 7
21272 M0953015T 95352 SR 00 44 8
21272 M0953015T 95352 SR 00 46 9
21272 M0953015T 95388 SR 00 42 10
21272 M0953015T 95388 SR 00 44 11
21272 M0953015T 95388 SR 00 46 12
31903 M17661092 001 SI 30 30 1
31903 M17661092 001 SI 30 32 2
31903 M17661092 001 SI 30 34 3
31903 M17661092 001 SI 30 36 4
31903 M17661092 001 SI 30 38 5
31903 M17661092 001 SI 30 40 6
31903 M17661092 001 SI 32 30 7
31903 M17661092 001 SI 32 32 8
31903 M17661092 001 SI 32 34 9
31903 M17661092 001 SI 32 36 10
31903 M17661092 001 SI 32 38 11
31903 M17661092 001 SI 32 40 12
31903 M17661092 001 SI 34 32 13
31903 M17661092 001 SI 34 34 14
31903 M17661092 001 SI 34 36 15
31903 M17661092 001 SI 34 38 16
How can this be done without using cursors and/or loops. I know it looks pretty simple there but there's over 40,000 lines in that temp table and I'm gonna have to run this a few times a day while we're testing this new system. I could program this easily in a cursor but I know it's gonna be slow.
August 20, 2008 at 9:42 am
You can use the Row_Number function to set the sequence. It works like this:
Row_Number() Over(Partition By ProdNum Order By ProdNum)
Partition By says to restart the numbers when ProdNum changes. The Order By is required so you start with your Partition By and can add anything else to the Order By Like if you wanted to sequence by color ascending you would add Color to the Order By.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 20, 2008 at 9:49 am
As this is a RANKING problem, any solution will require that the rows be ordered and some alternative are:
1. Code your own ranking but this is not a good solution as described in Jeff Moden's article titled "Hidden RBAR: Triangular Joins" at
at http://www.sqlservercentral.com/articles/T-SQL/61539/
2. Use a cursor, which for RANKING problems works fine.
3. Use the RANK function
RANK() OVER
(PARTITION BY Prodnum
ORDER BY ? DESC) AS 'RANK'
Noticed that the data you provided has duplicate values e.g. there are two identical rows, and this will need to be "fixed" before the RANK function can be used or duplicate ranks will be assigned.
SQL = Scarcely Qualifies as a Language
August 20, 2008 at 10:22 am
I tried both methods and they both worked fine.
Thanks a lot, you two just saved me a lot of time in the next few weeks spent doing...........nothing but wait for cursors to complete. 😉
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply