How to append information to simmilar data

Hi

I'm currently pulling out our user information for the tech guys to set up our new email system. Unfortunately they've decided to go with forename.surname as the username to make life easier so people wont always be forgetting their usernames.

Now as you can imagine using this system there are quite alot of duplicates. I've wrote two queries one which pulls out all the unique forename.surname users and one which gives me all the duplicates usernames.

What I was wondering is if its possible to append these usernames when pulling out the info so that instead of

john.smith , field2, field3
john.smith , field2, field3
john.smith , field2, field3

I get

john.smith1 , field2, field3
john.smith2 , field2, field3
john.smith3 , field2, field3

The rest of the fields are different for every user so they are not true duplicate data.

Below is the sql statement I'm using to pull out the duplicate users.

select stu_fnm1||'.'||stu_surn as username, field2, field3 ect... from table1
where (stu_fnm1||'.'||stu_surn) in (select (stu_fnm1||'.'||stu_surn) from table1
GROUP BY stu_fnm1||'.'||stu_surn
HAVING Count(stu_fnm1||'.'||stu_surn)>1) order by username

Any Suggestions would be appreciated as otherwise I'll prob have to go through all those records and do it manually.

Thanks

Derek Received on Fri Aug 08 2003 - 04:57:15 CDT