Complex re-number one column based on two column

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 2000 Forums

Transact-SQL (2000)

Complex re-number one column based on two column

Author

Topic

webforyou
Starting Member

15 Posts

Posted - 2009-03-04 : 03:56:27

Dear all,
I have a complex sql script to write "Re-number one column based on two column".
It means I want to renumber the Product starting from 1 for the old minimum Product, then increase by 1 for other products. This is done just for each policy.
Ex:
My old data:
Policy Product
100989 3
100989 4
100989 5
100989 6
100001 1
100001 2
100001 3
201000 2
201000 3
201000 4
201000 5
201000 6
201000 7
201000 8

My new data:
100989 1
100989 2
100989 3
100989 4
100001 1
100001 2
100001 3
201000 1
201000 2
201000 3
201000 4
201000 5
201000 6
201000 7

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-04 : 04:26:35

try this
declare @tab table(Policy int,Product int)
insert into @tab select 100989,3
insert into @tab select 100989,4
insert into @tab select 100989,5
insert into @tab select 100989,6
insert into @tab select 100001,1
insert into @tab select 100001,2
insert into @tab select 100001,3
insert into @tab select 201000,2
insert into @tab select 201000,3
insert into @tab select 201000,4
insert into @tab select 201000,5
insert into @tab select 201000,6
insert into @tab select 201000,7
insert into @tab select 201000,8

select identity(int,1,1) as rid,* into #temp1 from @tab

Select M.Policy,seq
from
(Select Policy,Product,(Select Count(*) from #temp1
Where Policy = Z.Policy and rid <= Z.rid ) as Seq
from #temp1 Z)M

drop table #Temp1

webforyou
Starting Member

15 Posts

Posted - 2009-03-07 : 12:18:05

Many thanks for your code. It works as I want and better than if I use the fetch cursor approach.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources