SQL query problems transpose MySQL

Question

There are 2 simple tables

People:
person_id
Name 

Reading assestment
date
person_id
quality
speed

Trying to create sql query

SELECT 
  AVG(r.quality),
  AVG(r.speed),
FROM reading_assestment r,people p
where r.person_id =p.person_id
  and person_id="3"

Current output:

Quality  Speed
77      65   

Outcome I am looking for:

Assestment Value
Quality      77
Speed        65

It is something to do with transpose , pivots.

Answer 1

SELECT
  AggResults.person_id   AS person_id,
  Assesment.Name         AS AssessmentName,
  CASE WHEN Assessment.Name = 'Quality' THEN AggResults.AvgQuality
       WHEN Assessment.Name = 'Speed'   THEN AggResults.AvgSpeed
                                        ELSE NULL
  END                    AS AssessmentValue
FROM
(
  SELECT
    people.person_id   AS person_id,
    AVG(quality)       AS AvgQuality,
    AVG(speed)         AS AvgSpeed
  FROM
    reading_assessment
  INNER JOIN
    people
      ON reading_assessment.person_id = people.person_id
  GROUP BY
    people.person_id
)
  AS AggResults
CROSS JOIN
(
  SELECT 'Quality' AS Name
  UNION ALL
  SELECT 'Speed'   AS Name
)
  AS Assessment
WHERE
  AggResults.person_id = 3

I moved the = 3 to the outer query, just for ease of use. Most optimisers will treat both options similarly.

Answer 2

The most general way is to start with your query and then unpivot using separate logic:

select (case when n = 1 then 'Quality' else 'Speed' end) as Assessment,
       (case when n = 1 then avg_quality else avg_speed end) as Value
from (select AVG(r.quality) as avg_quality, AVG(r.speed) as avg_speed
      from reading_assestment r join
           people p
           on r.person_id =p.person_id
      where person_id = 3
     ) t cross join
     (select 1 as n union all select 2) n