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.
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 | description1199 | 0 | job code bullet 1 description1199 | 0 | job code bullet 2 decription1199 | 0 | job code bullet 3 description1199 | 1 | job code bullet 1 description1199 | 1 | job code bullet 2 description1200 | 0 | job code bullet 1 description1200 | 0 | job code bullet 2 descriptionThe 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 | count1199 | 0 | job code bullet 1 description | 11199 | 0 | job code bullet 2 decription | 21199 | 0 | job code bullet 3 description | 31199 | 1 | job code bullet 1 description | 11199 | 1 | job code bullet 2 description | 21200 | 0 | job code bullet 1 description | 11200 | 0 | job code bullet 2 description | 2Basically 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] |
|
|
|
|
|