SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Need help with SQL update/loop
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

disbeliever
Starting Member

1 Posts

Posted - 06/21/2010 :  10:29:26  Show Profile  Reply with Quote
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)

Singapore
17634 Posts

Posted - 06/21/2010 :  10:34:42  Show Profile  Reply with Quote

select *, [count] = row_number() over (partition by code, [code ext] order by description)
from   yourtable



KH
Time is always against us

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000