Multiple rows, one query

Question

Let's say I have the following table, called GPAs:

name | semester | GPA
Joe    Winter     3.5
Joe    Spring     4.0

How can I return the following in one query?

name | gpaWinter | gpaSpring

Joe      3.5          4.0

Answer 1

SELECT name, (SELECT GPA FROM GPAs WHERE name = t1.name AND semester = 'Winter') AS `gpaWinter`, (SELECT GPA FROM GPAs WHERE name = t1.name AND semester = 'Spring') AS `gpaSpring` FROM GPAs t1

But you should really not be doing this in SQL. The purpose of SQL is to retrieve the data; formatting it for display is the job of the application code.

Answer 2

select name, 
    max(case when semester = 'Winter' then GPA end) as gpaWinter, 
    max(case when semester = 'Spring' then GPA end) as gpaSpring
from GPAs
group by name

Answer 3

SELECT name,GROUP_CONCAT(semester) AS semesters,GROUP_CONCAT(GPA) AS GPAs
FROM GPAs
GROUP BY name

This will return a row like:

name |   semesters   |  GPAs
Joe    Winter,Spring   3.5,4.0

Then you can parse semesters and GPAs.

You can also do something like GROUP_CONCAT(semester,'=',GPA) as GPAs, which will return:

name    |         GPAs
Joe       Winter=3.5,Spring=4.0

Then you can parse the GPAs row.