Pivot table without GROUP_CONCAT

from the Artful Common Queries page

Data designs often require flexibility in numbers and names of data points per instance row: instead of saving all the data points belonging to a key value in a single row, you save each data point as a name-value pair in its own row. Thus given table user_class(user_id INT, class_id CHAR(20), class_value CHAR(20)) with these rows: 
user_id  class_id   class_value
1        firstname  Rogier
1        lastname   Marat
2        firstname  Jean
2        lastname   Smith
and you wish a resultset that links first names to last names for each ID... 
user_id  firstname  lastname
1        Rogier     Marat
2        Jean       Smith
the following query accomplishes the required pivot via an INNER SELF-JOIN: 
SELECT 
  u1.user_ID,
  class_value AS firstname,
  u2.lastname 
FROM user_class AS u1
INNER JOIN (
  SELECT 
    user_ID,
    class_value AS lastname 
  FROM user_class
  WHERE class_id='lastname'
) AS u2 
ON u1.user_ID=u2.user_ID AND u1.class_id='firstname'

Last updated 22 May 2024