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'
|
|