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'


Return to the Artful Common Queries page