SQL how to map a row to column?

Question

Database structure with two 1-n connections.

User table
==========
user_id

Attribute table
===============
attribute_id
user_id
attribute_name

Attribute_Value table
=====================
attribute_value_id
attribute_id
attribute_value

Is there a way that I can receive the data in the following row style:

user_id | firstname | lastname
---------------------------
1       | Simon     | Smith
2       | John      | Doe

Where name is the first attribute_name entry from the Attribute table and lastname the second.

Answer 1

The only way you can do it with a dynamic number of columns is to use dynamic sql- use one sql statement to generate another by string concatenation.

Edit: I am not sure this is possible in mysql since there's not pivot command. Let me know if you want to see an example query from MS Sql I wrote. It is a similar data model, but there's just a lot more fields / joins.

Also I want to point out that you have a flaw in your design if attribute names wind up not being unique.

Answer 2

Since you know the names of the values that you are looking for, a join will do the trick.

select
  u.user_id,
  a1.attribute_value as firstname,
  a2.attribute_value as lastname
from User u
inner join Attribute a1
on u.user_id = a1.user_id
and a1.attribute_name = 'name'
inner join Attribute_Value v1
on a1.attribute_id = v1.attribute_id
inner join Attribute a2
on u.user_id = a2.user_id
and a2.attribute_name = 'lastname'
inner join Attribute_Value v2
on a2.attribute_id = v2.attribute_id

or so (did not run this). Use left joins if not everybody has a first or last name.