Joining name/value data to flat data
I have 2 tables, tblAccount and tblAttribute. tblAccount is a 1 row per user affair, whereas tblAttribute is a name/value table. What I need to do is to combine all the data into a 1 row per user query. Something like:
SELECT tblAccount .*,
tblAttribute.Value AS EmploymentOccupation WHERE tblAttribute.Name='EmploymentOccupation',
tblAttribute.Value AS EmploymentStatus WHERE tblAttribute.Name='EmploymentStatus'
INNER JOIN tblAttribute ON tblAccount.ID = tblAttribute.ID
I know this isn't the way to do this, but any pointers gratefully received.