In cross-tabulation, the result set's data is divided into header and data sections, separated by a cross-tabulation column. Header columns, located left from the cross-tabulation column (and optionally also on the right), determine the distinct rows in the final result set. The data columns are then repeated under distinct values derived from the cross-tabulation column.
The purpose of cross-tabulation is to transform the table from its original structure, such as:
into this:
When using cross-tabulation, the order of the rows in the report will be determined by the order of the data in the query.
Likewise, the arrangement of data columns is determined by the order of the data in the query.
If you wish to specify the order of data columns or if your data might not include all the data columns,
you can employ the dbr.crosstab.col command to predetermine the data columns.
Say we have a basic data set:
select RepArea as 'Reporting area',
Period,
last_year as 'Last year',
this_year as 'This year',
next_year as 'Next year'
from mydb.ProductionSummary;
We can determine the different parts of the result set:
By designating the column 'Period' as a cross-tabulation column, the unique values in that column are computed as group columns, and the data columns are calculated beneath each group column.
If any other column includes myDBR commands, they are automatically processed. In the example, we will employ styles and some aggregate functions in the data columns.
/*
Define the Period as a cross-tabulation column
Make sure you remember to order the result set accordingly
*/
select 'dbr.crosstab', 'Period';
/* Highlight the current year */
select 'dbr.colstyle', 'this', mydbr_style('Diff colors with bg');
/* Calculate sums */
select 'dbr.sum', 'last','this','next';
select RepArea as 'Reporting area',
Period,
last_year as 'Last year[last]',
this_year as 'This year[this]',
next_year as 'Next year[next]'
from mydb.ProductionSummary;
See full description of Cross-Tabulation command.