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!
It would be useful, if that still doesn’t work and it is possible, to allow me access to an API endpoint that returns this test data so I can work with your specific data structure/content
If possible, could you send me the URL as a private message?
Hi @Antony
Another update, rearranged the loops and weened out what I think was the issue with the previous code:
function formatter_qpivot($arr1, $idcol, $headcol, $propvalue){
// expects query result and parameters for the names of the id/groupBy column, the header column and the column to use for values
// get all unique header values
$phs = array();
foreach ($arr1 as $h) {
$thish = preg_replace('/\s+/', '_', $h[$headcol]);
if(!in_array($thish,$phs)){
$phs[] = $thish;
}
}
$uniquePhs = array_unique($phs);
$retvals = array(); // initialise return array
foreach($arr1 as $row) {
$thisid = preg_replace('/\s+/', '_', $row[$idcol]); // replace spaces with underscore for id for use as property name
if(!isset($retvals[$thisid])) {
$retvals[$thisid] = array($idcol => $row[$idcol]);
foreach ($uniquePhs as $head){
$retvals[$thisid][$head] = null;
}
}
$thishead = preg_replace('/\s+/', '_', $row[$headcol]); // replace spaces with underscore of pivot headers
$retvals[$thisid][$thishead] = $row[$propvalue];
}
return $retvals;
}
However, I need to export it to a CSV, and for that I need to have it as an array with [0], [1], etc as the headers (like my attendees output below), rather than the names. Is that an easy change for you to make?
