Hi,
I have two tables, the first one has these columns
Table Name: jos_rsform_submission_values
Columns: SubmissionValueId, FormId, SubmissionId, FieldName, FieldValue

Sample Data:
"101855","15","4284","Are you Human","garm"
"101854","15","4284","ACT",
"101853","15","4284","SAT",
"101852","15","4284","GPA",
"101851","15","4284","High School Name","Radnor High School"
"101850","15","4284","Coachs Phone",
"101849","15","4284","Coachs Name",
"101848","15","4284","Statistics",
"101847","15","4284","Shoots","Right"
"101846","15","4284","Positions","Center "
"101845","15","4284","League","Metropolitan Junior Hockey League"
"101844","15","4284","Current Team","Philadelphia Little Flyers "
"101843","15","4284","Height","5'11''"
"101842","15","4284","Weight","155"
"101841","15","4284","Date of Birth","06/30/1993"
"101840","15","4284","Email","example@email.co m"
"101839","15","4284","Zip","19010"
"101838","15","4284","Phone","123 124 1234"
"101837","15","4284","State","PA"
"101836","15","4284","City","Rosemont"
"101835","15","4284","Address","32 Wentworth Lane"
"101834","15","4284","Name","John Doe"
----
Table Name: jos_rsform_submissions
Columns: SubmissionId, FormId, DateSubmitted, UserIp, Username, UserId

Sample Data:
"4284","15","2010-03-22 07:47:36","204.13.205.98",,"0"


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.

Any idea on how to do this?

Thanks!!!

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

Code:

create function get_attribute(
        in_id           int,
        in_param        varchar(50) )
        returns varchar(250)
begin
        /*
        **      get value
        */
        declare out_value               varchar(250);

        select  max(FieldValue) into out_value
        from    jos_rsform_submission_values
        where   SubmissionId = in_id
                and FieldName = in_param;

        return ifnull(out_value,'');
end

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 :

Code:

select get_Attribute( Submission_Id,'Name'), 
       get_Attribute( Submission_Id,'Email'),
       get_Attribute( Submission_Id,'Coachs Name'),
       get_Attribute( Submission_Id,'Coachs Phone')
from   jos_rsform_submissions
where  get_Attribute( Submission_Id,'Shoots') = 'Straight'
       and get_Attribute( Submission_Id,'Weight') >= 200
       and get_Attribute( Submission_Id,'City') = 'Rosemont'

Mike