MySQL query to output data with column headings from table

Question

Is there a way in SQL of getting the data shown in the left table to be output as in the picture on the right?

Answer 1

If you have a finite, static list of tests, and no user taking a test more then once, you can do it like this:

SELECT UserID, 
       SUM(Result * (Unit = "Test 1")) AS "Test 1",
       SUM(Result * (Unit = "Test 2")) AS "Test 2",
       SUM(Result * (Unit = "Test 3")) AS "Test 3" 
FROM table
GROUP BY UserID;

This works by using SUM over a GROUP as a weighted sum of all entries with a weight of 1 for the line with the matching test and 0 for all other lines (the comparison evaluates to 1 for TRUE and 0 for FALSE).

Edit

As @Nick pointed out in his comment, you can go a more readable and less implementation-dependent way using a CASE instead of the multiply-by-boolean style:

SELECT UserID, 
       SUM(CASE WHEN Unit = "Test 1" THEN Result ELSE 0 END) AS "Test 1",
       SUM(CASE WHEN Unit = "Test 2" THEN Result ELSE 0 END) AS "Test 2",
       SUM(CASE WHEN Unit = "Test 3" THEN Result ELSE 0 END) AS "Test 3"
FROM table
GROUP BY UserID;