So I have a mysql database that contains entries from a form. 1 column represents the form ID, the next the itemID (which is unique for all question/answer combinations, a question column and an answer column. The forms can contain dynamic data where there is a repeat of the same set of questions as per below.
The mysql table looks similar to this -
| formID | itemID | Questions | Answers |
|---|---|---|---|
| 1 | 1 | question1 | No |
| 1 | 2 | question2 | Maybe |
| 1 | 3 | question3 | yes |
| 1 | 4 | question4 | Yes |
| 1 | 5 | question5 | No |
| 1 | 6 | question6 | Maybe |
| 1 | 7 | question4 | Maybe |
| 1 | 8 | question5 | Maybe |
| 1 | 9 | question6 | Maybe |
| 1 | 10 | question4 | Other |
| 1 | 11 | question5 | Yes |
| 1 | 12 | question6 | Maybe |
| 2 | 13 | question1 | No |
| 2 | 14 | question2 | No |
| 2 | 15 | question3 | Maybe |
| 2 | 16 | question4 | Yes |
| 2 | 17 | question5 | No |
| 2 | 18 | question6 | Other |
| 2 | 19 | question4 | No |
| 2 | 20 | question5 | Maybe |
| 2 | 21 | question6 | Other |
I want the table to look like this
| formID | question1 | question2 | question3 | question4 | question5 | question6 |
|---|---|---|---|---|---|---|
| 1 | No | Maybe | Yes | Yes | No | Maybe |
| Maybe | Maybe | Maybe | ||||
| Other | Yes | Maybe | ||||
| 2 | No | No | Maybe | Yes | No | Other |
| No | Maybe | Other |
As you can see the Questions repeat depending on how many dynamic entries are made in the form. I have tried case statements and although I do get close, it will not include the dynamic data. My sql query knowledge is limited and I have even looked at dynamic sql but I am lacking the knowledge to write it correctly and implement it for testing.
Any help would appreciated. If I can get the query right, I know someone who can php from there to convert to a .csv for exporting.