I have two tables, data and metadata. Metadata has these columns project_id(id of project, references Data's project_id), field_name(variable that that describes value, references Data's field_name) , element_label (label for field). Data has project_id (references metadata's project_id) , field_name (references metadata's field_name), value (the actual data value). I want it to output 3 things.
ex. project_id=1, field_name="Sex of person", element_label="SEX", value="MALE"
project_id=1, field_name="Age of person", element_label="AGE", value="20"
I want the element_label to be the "columns" in the finished query plus have a final column that shows the project_id (don't need field_name, but thought it might be used for referencing tables). And under these element_label columns" to have the corresponding values. I am completely stuck how to do this, please help!
I know the metadata is horrible. Its possible because this is actually a program I am using, I am just accessing the database it stores everything in. The program itself can't create reports such as this but is limilted in how many fields and such.