3

Over the past few days, I have been trying to find an answer to this problem. While I have not found an answer, this site keep appearing in my search results so I thought I would give it a try. Awesome formatting options for posts BTW.

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        5554443333  
2         1         1             firstName    Sally  
2         1         2             lastName     Jones  
2         1         3             phone        3334445555

that I want to turn into: 

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

I can find example of how to do this. I don't understand them very well but they are out there. My unique problem comes into effect when this single table holds data entered from a variety of forms with any possible number of fields. So, I might 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        5554443333  
2         1         1             firstName    Sally  
2         1         2             lastName     Jones  
2         1         3             phone        3334445555  
3         2         1             fav_color    red  
3         2         2             fav_animal   eagle  
4         2         1             fav_color    blue  
4         2         2             fav_animal   dog

I will then pass in the form_id as a parameter, gather all records from that form grouped by users (they should all have the same labels with different values), and then display that data as:

...when form_id = 1 the report looks like: 

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

...when form_id = 2 the report looks like: 

user_id | fav_color | fav_animal  
3         red         eagle  
4         blue        dog

I am new to advanced SQL programming and procedures and am not able to figure out how to do this on my own. I need the query to be able to handle any number/type of fields without having to enter the exact possible field names into the query for each form. Notice the first query immediately above has four fields while the second has 3. So the solution needs to be flexible in this manner.

The SQL can also be generated from a programming language so there are more options with that solution if that helps.

Please inform me if you do not have enough information.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Darren Spafford
  • 33
  • 3

1 Answers1

3

You could try (untested) 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
BugFinder
  • 17,474
  • 4
  • 36
  • 51
  • Thank you for your response. I can't believe I have not seen this until now but since I can create the SQL statement programatically, I can first query the data for the field names and then dynamically create the SQL statement you provided for each type of form. The only change I made to the above statement was to change "group by form_id" to "group by user_id" so I get each users response vs. the first response for that form. Thank you again for your help! – Darren Spafford May 14 '11 at 08:23