title: Flattening an EAV type SQL query
body: <p>I have a MySQL database used for a mailing list, and I need to extract the data, so one member record is represented by one row.  The mailing list stores the user attributes as EAV.  I can extract all the details I need using the following  SQL query, but each member record takes up multiple rows:</p>
  
  <pre><code>SELECT a.id, a.name, a.email, b.id, b.name, b.email, c.title, d.val
  FROM lists a, listmembers b, fields c, fieldsdata d
  WHERE a.id = b.nl
  AND b.id = d.eid
  AND c.id = d.fid
  ORDER BY b.id, a.id, b.name
  </code></pre>
  
  <p>This returns something like this:</p>
  
  <pre><code>'6', 'Mailing List name', 'owner@mailinglist.com', '10478', 'username', 'mailinglistmember@emailaddress.com', 'Firstname', 'John'
  '6', 'Mailing List name', 'owner@mailinglist.com', '10478', 'username', 'mailinglistmember@emailaddress.com', 'Lastname', 'Smith'
  '6', 'Mailing List name', 'owner@mailinglist.com', '10478', 'username', 'mailinglistmember@emailaddress.com', 'Country', 'UK'
  '6', 'Mailing List name', 'owner@mailinglist.com', '10478', 'username', 'mailinglistmember@emailaddress.com', 'Town', 'Cambridge'
  '6', 'Mailing List name', 'owner@mailinglist.com', '10478', 'username', 'mailinglistmember@emailaddress.com', 'Shoesize', '7'
  '6', 'Mailing List name', 'owner@mailinglist.com', '10478', 'username', 'mailinglistmember@emailaddress.com', 'Favourite Colour', 'Purple'
  </code></pre>
  
  <p>I need to flatten this into one row using SQL, only requiring values relating to the keys firstname, lastname, town and country</p>
  
  <p>The database is not huge, the fieldsdata table being the largest with about 5500 rows.</p>
  
  <p>Seems like a real pain, so any pointers would be gratefully received. !</p>