What is a pivot table ?
A pivot table is just the same table where :
- the pivot column has been suppressed
- for each distinct value of the pivot column, a column with a conditional sum is created.
Example : If I want to pivot this table from the SH schema :
|Promo Category||Channel Desc||Amount Sold|
|NO PROMOTION||Direct Sales||115,549,450|
If the pivot column is the column “Channel Desc”, we have 5 distinct values :
Direct Sales Internet Partners Tele Sales
And the pivot table will result of this sql statement :
select "Promo Category", sum(case "Channel Desc" = 'Direct Sales' then Amount Sold else 0 end) "Direct Sales", sum(case "Channel Desc" = 'Internet' then Amount Sold else 0 end) "Internet", sum(case "Channel Desc" = 'Partners' then Amount Sold else 0 end) "Partners", sum(case "Channel Desc" = 'Tele Sales' then Amount Sold else 0 end) "Tele Sales" from FactTable, DimensionTable, ... group by "Promo Category";
Transformation of a table in a pivot table
For the query above, we need 6 column :
- one for the “Promo Category”
- 4 formula columns for each distinct value of the “Channel Desc”
In each formula column and for each distinct value of “Channel Desc”, enter a filter formula.
Below an example for the value 'Direct Sales' :
FILTER("Sales Facts"."Amount Sold" USING (Channels."Channel Desc" = 'Direct Sales'))
And here for the value “Internet” :
This method has a weak point is that you must know the number of distinct value in advance. For instance, if a new distinct value appear in the column “Channel Desc”, you must change your report.
Below the pivot view :