How to add an identity column to a table with existing data

[kostasgio]

Hello, this is my first post here, i hope i'll find this forum usefull.

Although i did a search about my question, i didnt find what i need , because the question isnt exactly what it sounds.

Here's my scenario

I have a table that doesnt have an identity column, but does have unique rows depending on a combination of two columns.
the table's name is carowner and the two columns i am talking about are iteid,cusid and they are both of datatype int. what i need to do is to add to the existing data of the table a linenumber per iteid. for example i could have the following two records
iteid | cusid
6668 | 1
6668 | 2
6669 | 1
6669 | 4

what i wanna do is add a column so that the above data would look like this

iteid | cusid | Linenum
6668 | 1 | 1
6668 | 2 | 2
6669 | 1 | 1
6669 | 4 | 2

So i need to number the records depending on the column iteid and i need to do that for the existing data but also for every new record inserted into the table.
I was thinking this could be done by adding a trigger to the table but i am not sure on how to do this.

Any help woould be greatly appriciated, so many thanx in advance.

[nbiswas]

Solution to How to add an identity column to a table with existing data

Try this

declare @t table(itemid int,cusid int)
insert into @t 
	select 6668,1 union all
	select 6668,2 union all
	select 6669,1 union all
	select 6669,4

Query:(Sql server 2005+)

select 
t.*
,ROW_NUMBER() over(partition by itemid order by itemid) Linenum
from @t t

Query (For Sql Version less than 2005)

select *, ( select count(*)
from @t t2
where t2.itemid = t1.itemid
and t2.cusid <= t1.cusid
) as Linenum
from @t t1

Output:

itemid cusid Linenum

6668 	1	           1
6668	        2	           2
6669	        1	           1
6669	        4	           2

Hope this helps

[kostasgio]

Nbiswas, this was just what i needed, worked like a charm!
Thanks a lot for your help :)