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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-05-16 : 10:25:50
|
| Greg writes "Hello,I have designed as such : id,firstname,lastname,address,phone,emailThe problem is I have recently imported everyone's contacts from around the office, so as you can imagine there's some pretty screwed up data in there, not to mention the amount of dupe's I've got!The problem with the dupes is some people have more info than others, as such:234, Greg, Fyans, , ,greg@here.com643, Greg, , , 0141 14141414, greg@here.com925, Greg, Fyans, 45 Dudley Drive, , greg@here.comSo as you can see, blatantly the same person just with 3 different entries, I'm trying to find a way to flatten down 3 rows into one, and keep all the data!!!So far...very unsuccessful! have managed to delete all rows about 3 times already.Hope to hear some good suggestions, cus I'm stumped!!!Thanks,Greg.(Windows 2000, SQL Server 2000, can also code ASP if the need be)" |
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-05-16 : 11:54:17
|
| okies, one little criteria question...234, Greg, Fyans, , ,greg@here.com 643, Greg, , , 0141 14141414, greg@here.com 925, Greg, Fyans, 45 Dudley Drive, , greg@here.com you would like this reduced to id, greg, fyans, 014114141414 45 dudley drive, greg@here.com?or did you just want to keep the top record and let the other 2 records disappear? |
 |
|
|
joldham
Wiseass Yak Posting Master
300 Posts |
Posted - 2002-05-16 : 12:56:45
|
| Not sure if this does exactly what you want, but here it is.Select Fname, max(lname), max(address), max(phonenumber), emailInto New_ContactsFrom contactsWhere fname is not nulland email is not nullGroup by Fname, emailThis is assuming that you have emails and first names populated for everyone. If you do not have emails for everyone, you might have to write another query similar to the one above to get all the information for people without emails or first names.Jeremy |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-05-16 : 16:11:51
|
Of course we don't know the whole story here, but its sounds to me like you are trying to fix the effect rather than the cause. Maybe you should think about putting a unique constraint on email and altering your setemail proc to something like...alter proc setemail <parms>asupdate where email = @emailinsert where not exists (select 1 from <tablename> where email = @email)go u dig?<O> |
|
|
|
|
|
|
|
|
Flattening rows with same email address, but not the same address, phone number etc... into one row!