MySQL pivot table

Question

In the past few days, I have been trying to find the answer to this problem. Although I did not find the answer, this site continues to appear in the search results, so I decided to give it a try. Amazing BTW message formatting options.

I have a table like:

user_id | form_id | question_id | data_label | data_value 1 1 1 firstName Joe 1 1 2 lastName Smith 1 1 3 phone [phone] firstName Sally 2 1 2 lastName Jones 2 1 3 phone [phone] 

which I want to go to:

 user_id | firstName | lastName | phone 1 Joe Smith 5554443333 2 Sally Jones 3334445555 

I can find an example of how to do this. I do not understand them very well, but they are there. My unique problem comes into force when data entered from various forms with any possible number of fields is stored in this separate table. So, I can have a table with:

 user_id | form_id | question_id | data_label | data_value 1 1 1 firstName Joe 1 1 2 lastName Smith 1 1 3 phone [phone] firstName Sally 2 1 2 lastName Jones 2 1 3 phone [phone] fav_color red 3 2 2 fav_animal eagle 4 2 1 fav_color blue 4 2 2 fav_animal dog 

Then I will pass to form_id as a parameter, collect all the entries from this form, grouped by users (they should all have the same labels with different values), and then display this data as:

... When form_id = 1, the report looks like this:

 user_id | firstName | lastName | phone 1 Joe Smith 5554443333 2 Sally Jones 3334445555 

... When form_id = 2, the report looks like this:

 user_id | fav_color | fav_animal 3 red eagle 4 blue dog 

I am new to advanced programming and SQL procedures and cannot figure out how to do this on my own. I need a query in order to be able to process any numbers or types of fields without having to enter possible field names for each form in the query field. Please note that the first query directly above has four fields, and the second has 3. Therefore, the solution must be flexible in this way.

SQL can also be generated using a programming language, so if this helps, there are more options with this solution.

Please let me know if you do not have enough information.

Answer 1

You can try (unchecked) something like

 select form_id, max(case data_label when 'lastName' then data_value else null end) as lastname, max(case data_label when 'firstName' then data_value else null end) as firstname, max(case data_label when 'phone' then data_value else null end) as phone from mytable group by form_id