Hi There

I have a table that contains contact No's (telephone) for dog owners.
A dog owner can have more than one contact no (obviousley). The table fields are:

contact_dialler_1, contact_dialer_2 and contact_dialer_3

Contact_dialer_1 = "D","C","A" for day, cell and After hours

I would like to select all contact data and have one (1) line for each contact by putting the contact no's in there own fields eg:

name day_phone Cell_phone After_hours
Greg 2392270 021 1879923 2345567

Can any one help with how to select the Contact_dialer fields so that there is only one line per contact and not two or three depending on how many telephone no's they have.

Thank

Greg

From what you have posted so far, it is not clear what your source data structure is.

From the looks of it you already have three fields for each person. However, from the sound of your question you have many records for each person and you want to wrap them up into a single line. Which is it?

It would help us to see your table schema for the source data.

-Peter
compiledthoughts.com

Hey Peter,

Sorry to reply so late but I've been away on course and a bit of leave.
There are three tel no's for each contact but sometimes there are more than one no for a type - sy day_phone. so i could have

name day_phone Cell_phone After_hours
Greg 2392270 021 1879923 2345567
        2395562
        2354892

I would like to pick only one for each type and get the result of

name day_phone Cell_phone After_hours
Greg 2392270 021 1879923 2345567

Cheers

greg

You could use the MAX() or MIN() aggregation function. You'll have to do this for all fields selected that aren't grouped such as:

SELECT name, MAX(day_phone), MAX(Cell_phone), MAX(After_hours)
GROUP BY name

Although I would make the argument that there is no point in storing more than one number for a given type of you are going to simply ignore all but one of them. Database normalization rules would also suggest that you should put all the phone numbers in a single column in a "phone numbers" table and specify the type of each one. This might make the data easier to work with as well.

-Peter
compiledthoughts.com