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 2005 Forums
 Transact-SQL (2005)
 Need help with SQL update/loop

Author  Topic 

disbeliever
Starting Member

1 Post

Posted - 2010-06-21 : 10:29:26
Hello,
I have a table that has data in this format:

code | code ext | description
1199 | 0 | job code bullet 1 description
1199 | 0 | job code bullet 2 decription
1199 | 0 | job code bullet 3 description
1199 | 1 | job code bullet 1 description
1199 | 1 | job code bullet 2 description
1200 | 0 | job code bullet 1 description
1200 | 0 | job code bullet 2 description

The table contains job data. Each code can have multiple extensions. I need to figure out how to loop through this data, and add an auto increment count for each unique job code and extension and then reset, continue on and continue counting. So for example here is the new format I need of the above data:

code | code ext | description | count
1199 | 0 | job code bullet 1 description | 1
1199 | 0 | job code bullet 2 decription | 2
1199 | 0 | job code bullet 3 description | 3
1199 | 1 | job code bullet 1 description | 1
1199 | 1 | job code bullet 2 description | 2
1200 | 0 | job code bullet 1 description | 1
1200 | 0 | job code bullet 2 description | 2

Basically I need a bullet number count for each entry of a unique job code and extension. So since 1199-0 had 3 entries, it auto counts those 3. Then it sees that unique job code and ext is done, sees 1199-1 and starts the count of bullet items etc.

Can anyone ehelp?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-06-21 : 10:34:42
[code]
select *, [count] = row_number() over (partition by code, [code ext] order by description)
from yourtable
[/code]


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page
   

- Advertisement -