How to make a common id for more then one row

i have a table in my database given like bellow

  Requestid(primary key,identity)  studentid   reqid
    1                                1          bc1
    2                                1          bc1
    3                                2          bc2

I want to generate the same request id for student 1 if he is making more then one request. I am using SQL server 2005 and Requested id is identity and student id will come when i submit my form but i want to generate reqid as automaticaly. It is same for the the student of same id and when next student submit i开发者_运维知识库t should change with the new id.

Plz hel me to solve it. Thanks in advance

If I understand what you want you could use a computed column to generate reqid.

create table StudentRequest
(
  Requestid int identity primary key,
  studentid int not null,
  reqid as 'bc'+cast(studentid as varchar(10))
)

Test:

insert into StudentRequest (studentid) values (1)
insert into StudentRequest (studentid) values (1)
insert into StudentRequest (studentid) values (2)

select *
from StudentRequest

Result:

Requestid   studentid   reqid
----------- ----------- ------------
1           1           bc1
2           1           bc1
3           2           bc2