Pivot Datasets

Many reporting tools offer the ability to "Pivot" data, by turning relational rows into output columns; but sometimes (for example, for performance reasons) it is necessary to pre-build these results, as part of your daily data transformation routines. Data Academy's "Pivot" Datasets offer two key advantages over SQL Server's native functionality. Firstly, they are fairly straightforward to implement; and secondly, they transparently handle the problem of adding additional columns to your output, as new "types" appear in your source data.

We will examine Pivot Datasets by following-through an example based on the following source table, summarizing sales in a car dealership.

In the Transformation Space Details screen, add a Dataset of type "Pivot".

In the Dataset Details, add the YEAR and MONTH fields.

If you drill-into either of these fields, you will observer that they are marked (by default) as a "Row" in the "Row/ Column" option.

Now add the MODEL column and, drilling-in, change the Row/ Column option to "Column". You will then be able to identify SALES as the "Pivot Field". The "Pivot Field" is the field which will form the actual, cross-referenced data of our output - i.e. the data that appears in the body of the output table.

Note that we have left the Target Field as MODEL. This value will be prepended to out target column headers, so they will be named "MODELBMW". We may wish to tidy this up by appending an underscore to the value (so we would get "MODEL_BWM") or by removing it completely (so we would get just "BMW").

You are also able to set a "Group Function" to operate on the Pivot Field, specifying Count(), Sum(), etc. This would be applicable if, for example, we had just included the YEAR field, allowing us to roll-up the reported data, by summing all the monthly values.

WARNING: In version of Academy prior to 5.301, it is necessary to set a value for the Group Function, even when no grouping operation is being undertaken. In such cases we recommend setting the value to "Sum(#)".

Now all that remains is to run your Transformation Space and check the output! It should look something like this.