Say I have a database table (MySql for now) named 'objects', which is a very basic table:
ID - Name
Now I also have a table 'objectproperties':
ID - ObjectID - PropertyName - PropertyValue
Objectproperties can contain an unknown number of name value pairs which are additional properties for an object. So let's say there is an object id 33 with the name 'Test'. In objectproperties two properties are defined:
1 - 33 - Property1 - Value1
2 - 33 - Property2 - Value2
Now what I am trying to accomplish, is writing a query that produces the following result:
ObjectID - Name - Property1 - Property2
33 Test Value1 Value2
So the value of the column 'propertyname' in objectproperties should be appended as the column name of the resultset, with the corresponding propertyvalue as its value.
I've searched and found things like dynamic cross-tab sql, but I'm not sure if that is the way to go, if it can be done easier, etcetera. Help is really appreciated. Thanks in advance!
(attached is the create table scripts if necessary)
(I accidently ranked myself as guru on databases, I'm not, just intermediate or something)