So let's take the following example. I have a resultset that contains the following fields:
In this table we have several recurring values which look like this:
|... more data ...|
What I would like to have is a table that contains the sum of all the values (value_n) grouped by week, campaign and category. This is quite a common task and we use it pretty regularly. So I came up with the idea of creating a tag called <CFPIVOTTABLE> that does that exactly for me. The syntax for it is quite simple and here is the description for it so far:
This tag regroups a passed query into a pivot table depending on the field values. Please check the following example for usage details:
The query can be any query you receive from a <cfquery> tag. The columns refer to the column that should be used as a group column and the rows contain the values of that field for the row grouping. So the above call would return how many visitors have visited any location for any date.
In addition the tag adds a total column and a total row to the returning query. If you want to have group sums by row, just add the groupTotals attribute to the call of the tag.
|query||true||query||Query that should be converted into a pivot table.|
|columns||true||string||A list containing the columns to be grouped column wise.|
|rows||true||string||A list containing the columns to be grouped row wise.|
|values||true||array of structs||Array containing structs with fieldnames and operation for it. The struct contains a key field with the column name of the field and a key operation. Valid operations are: sum,avg and count.|
|columnsort||false||string||ASC||In which order should the columns be sorted.|
|groupTotals||false||boolean||false||Adds a sum row per grouped field.|
|totals||false||boolean||false||Adds a totals column for every row and column.|
|result||false||string||result||Name of the variable that contains the result.|
So we you use the CFPIVOTTABLE tag with the query above like this:
We will get the following result:
|10||Risk checks||sum : value_n||0||0||0||0||10||2||10||3||5||1||0||31|
|10||Responses email||sum : value_n||20||18||2||2||2||90||62||196|
|10||Responses||sum : value_n||0||64||27||10||14||7||7||0||321||270||28||748|
|10||Offers||sum : value_n||0||0||0||513||108||46||37||48||104||689||45||1590|
|10||Not reached||sum : value_n||0||6500||6500|
|10||New entry requests||sum : value_n||0||0||0||0|
|10||Addresses||sum : value_n||225152||225152|
|Sum for campaign_id||225152||84||45||525||134||55||54||53||7020||1022||73||234217|
I have the sample code as a ZIP file and the tag is called as a CFC based custom tag. You only need to copy the tag into the corresponding WEB-INF/railo/context/library/tag or serverconfig/context/library/tag directory and you can use it as an internal tag. I would appreciate any testing comments and suggestions.
At the moment, if you are selecting an additional value to group as a row and you have a total attribute selected, averages and counts are summed up to the total number, which of course falsifies the result. It was a little too complex for the short time I had to respect that as well. I will try to fix it ASAP.
If the feedback is good, I will convert it into an extension soon.