CODEWITHSUNDEEP
sqlsql-server
Rick
Rick

Reputation: 1579

Add identity numbers to specific column

I have a set of data where the combination of programid and rev_id makes primary key. My program Id is redundant for a different reason. So I want to add identity number to rev_id so the primary key rule is not violated.

My current data:

enter image description here

Expected result:

enter image description here

Any help?!

Rextester link: https://rextester.com/SJV93546

Upvotes: 0

Views: 38

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

I would suggest expressing this as:

select programID,
       (rev_id * 10 + row_number() over (partition by rev_id order by content asc) 
       ) as rev_id,
       content
from currentdata;

I see no reason to convert this to a string.

Upvotes: 0

zip
zip

Reputation: 4061

You can use row_number function with partition

select
programID,
cast(rev_id as varchar(6)) + cast(ROW_NUMBER() OVER(PARTITION BY rev_id ORDER BY content ASC) as varchar(2)),
content
 from currentdata

Upvotes: 2

Related Questions