What I want to do is write a query that will put these into a table form thats horizontal instead of vertical right now so instead of these columns: SubmissionValueId, FormId, SubmissionId, FieldName, FieldValue
The columns will be: Name, Address, City, State, Phone, Zip, Email, Date of Birth, Weight, Height, Athletic Information, Current Team, League, Positions, Shoots, Statistics, Coach Name, Coachs Phone, Academic Information, High School Name, GPA, SAT, ACT, Submit
with the database organized that way.. That way I can sort through these and pull out players that I need to email..
These column names are all in FieldName, and are the same throughout the table.
this is a perfect example of how difficult it is to pull meaningful information out of an EAV (entity-attribute-value) scheme
google that term for more information, you will see that nobody recommends it
as for achieving what you want, i suggest you pull all the rows you want into php (or whatever your application language is) and do the "horizontalization" there -- it'll be a lot easier than trying to do it with sql
As Rudy mentioned, using EAV doesn't make any sense here but if you want to access the data directly from the table then the easiest way is just to create a function that can pull a given attribute for a given entity ie
create function get_attribute(
in_param varchar(50) )
** get value
declare out_value varchar(250);
select max(FieldValue) into out_value
where SubmissionId = in_id
and FieldName = in_param;
I didn't pay too much attention to the field names etc but I'm sure you can work these out. Obviously this data would do better in a standard table but I assume the data is being generated by another application. You could then create "normal" queries pulling data from the original table like this :
select get_Attribute( Submission_Id,'Name'),
get_Attribute( Submission_Id,'Coachs Name'),
get_Attribute( Submission_Id,'Coachs Phone')
where get_Attribute( Submission_Id,'Shoots') = 'Straight'
and get_Attribute( Submission_Id,'Weight') >= 200
and get_Attribute( Submission_Id,'City') = 'Rosemont'