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:

  class_value AS firstname,
FROM user_class AS u1
    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'

Return to the Artful Common Queries page