How Can I Create a Pivot Table From Database Output?

I have a set of responses to a variable number of questions. They are stored in a separate responses table, which looks like this:


I’d like to create a pivot table type of output which has a variable number of columns, depending on the number of questions asked… so to look like this:

attendee_name       Accommodation       Meals                     Gender    ...
Percy Peck          Single Room(...)    Vegan Meal Option         Male 
Petra Parkes        Twin Room(...)      Pescatarian Meal Option   Female
Penelope Parker                         Pescatarian Meal Option   Female  

I’ve started doing it in a MySQL procedure and it may be possible to do but is quite tricky.

Maybe I can do it in a Server Action… and maybe the new features in v5 would do it but I’m still on 3.9.7 and won’t be changing any time soon…
(there is an add column feature in v3.9.7, but I can’t dynamically create the column name…)

Any thought on how to do it would be greatly appreciated!

Many thanks,

Community Page
Last updated: