CODEWITHSUNDEEP
mysql
Matthew
Matthew

Reputation: 7725

Multiple rows, one query

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

Upvotes: 0

Views: 133

Answers (3)

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171411

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

Upvotes: 2

gen_Eric
gen_Eric

Reputation: 227240

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.

Upvotes: 1

Dan Grossman
Dan Grossman

Reputation: 52372

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.

Upvotes: 1

Related Questions