transpose rows into columns

[Christian Meyer]

Hi,

in an existing db addresses (mail, phone, mobile) are stored in the same column, marked by a contacttype attribute:

id----address--------------contacttype_id-----person_id
[e-mail]--1--------------------1
2-----001783748483-----3--------------------1
[e-mail]----1--------------------2
4-----0093949394493----2--------------------1

Where contacttype 1 mail, 2 mobile, 3 landline.

I would like to get the following output:

person_id-------mail-----------------phone-------------mobile
[e-mail]---001783748483--0093949394493

I tried the following query, but it both columns the mail address appear:

SELECT m.address as mail, p.address as phone FROM contact m
INNER JOIN contact p on m.id = p.id WHERE m.contacttype_id = "1" OR p.contacttype_id = "2"

Any help is very appreciated,

many thanks

Christian



Edited 3 time(s). Last edit at 07/12/2009 09:16AM by Christian Meyer.