Transpose in database queries

Sometime you want to transpose data from rows into columns. For example, the person_extra table contains values that belong to persons, but the data is stored as rows. Here's a query to get the desired result:

 select 
  person_id, 
  MAX( CASE fieldname WHEN 'Person_fieldname1' THEN fieldvalue END) as Field1name, 
  MAX( CASE fieldname WHEN 'Person_fieldname2' THEN fieldvalue END) as Field2name, 
  email 
 from personextra 
  inner join person on person.id=personextra.person_id 
 where (fieldname = 'Person_fieldname1' or fieldname = 'Person_fieldname1') and person.isactive=1 and fieldvalue <>  
 group by person_id, email