How Can I Create a Pivot Table From Database Output?

Wappler General How To
#1

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

responses

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,
Antony.

#2

What server model?

#3

By Server Model, do you mean PHP?

#4

Yep.

You could use a custom formatter to do this. Give me a few mins…

1 Like
#5

This looks like it should do what you want (I think!)

updated formatter code in post below

use in a Set Value step like:

query.qpivot('attendee_name','question_topic','response')
1 Like
#6

You absolute superstar @bpj… I will give it a go!

Was spending time researching how to do custom formatters, and came across this you wrote which shows the way…

#7

Just curious… did it do what you wanted?

#8

Well almost! It is creating the table, but most of the fields are null…

Here are the lines in the server action:

server_actions1

Here is how response_list looks… so like my example in the original post:

response_list
response_list828×639 130 KB

But the pivot table only has the Any Questions field filled and the others are null…

pivot_table1
pivot_table1949×724 114 KB

It is all a bit above my head to try and debug it… but we are so close, I’m wondering if you have a solution @bpj!

Many thanks,
Antony.

#9

Just a tweak to replace spaces in created property names with underscores. It might not make the difference but it’s worthwhile:

Updated on post below
#10

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?

#11

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; 
}
1 Like
#12

Hi @bpj…

You are a superstar, that works.

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?

Many thanks!
Antony.

pivot_good
pivot_good882×410 118 KB

#13

I think you should be able to do a Set Value pointing to this pivot_table Set Value:

pivot_table.values()

And than use that for a CSV Export Step

If that doesn’t work, I can adjust the formatter

1 Like
#14

That works @bpj…

Thank you soooo much for all your help! :pray:

1 Like
#15

Glad it was useful. I may have a couple of places where it will be useful in my projects too.

1 Like