Takes data from a key:value table & combines it onto a single-row record, per item

gistfile1.txt

/*

Data starts in table:

ProjectId, key, value

1    totaltime  02:34:56
1    zip.url    http://www.mysite.com/download/file.zip
1    zip.size   12MB

etc

and a main table, Projects, with PK id

The 3 rows will combine onto a single row for each ProjectId, so will result in:

ProjectId   totaltime   zip_url                                   zip_size
   1        02:34:56    http://www.mysite.com/download/file.zip    12MB

*/

SELECT ProjectID, MAX(summed_data.totaltime) AS totaltime, MAX(summed_data.zip_url) AS zip_url, MAX(summed_data.zip_size) AS zip_size
FROM projects p, 
  (SELECT 
	   ProjectID, 
	   (CASE WHEN `key` = 'totaltime' THEN  `value` END) AS totaltime,
	   (CASE WHEN `key` = 'zip.url' THEN  `value` END) AS zip_url,
	   (CASE WHEN `key` = 'zip.size' THEN  `value` END) AS zip_size
	FROM project_files) AS summed_data
WHERE p.id = summed_data.ProjectID
GROUP BY ProjectID