In oracle it's possible to use the pivot function. This is not solely available for 1 column, but also for more than 1 column.
Simple Example
The base table contains records per hour of the number of cars passed by
The sum calculates the total number of cars for that day
The avg calculates the average number per hour for that day
SELECT *
FROM (SELECT w.wegnum road, day,
, frequence
FROM table)
PIVOT (AVG (frq) avg,sum(frq) sum
FOR day IN ('Ma' AS "MA",
'Di' AS "DI",
'Wo' AS "WO",
'Do' AS "DO",
'Vr' AS "VR",
'Za' AS "ZA",
'Zo' AS "ZO"))
Result
The results is a table with the columns weg and the individual days
ROAD MA_AVG MA_SUM DI_AVG .....
XX XX XX XX
Thursday, 23 August 2012
Oracle BI, break table after number of columns on same page
I have a report with a dynamic number of colomns. However, in the rtf-template you want to break the table after a predefined number of columns and continue on the next line.
You can use <?horizontal-break-table:number?>,however that breaks the table to a new page. The solution is (with a maximum of total columns of 100 and 5 columns on 1 row):
Instead of defining a 'hardcoded' value for maxcol of 100 the max number of columns can be calculated with distinct_values.
example:
<?variable:maxcol;count(xdoxslt:distinct_values(/FOTOLINKORG1))?>
You can use <?horizontal-break-table:number?>,however that breaks the table to a new page. The solution is (with a maximum of total columns of 100 and 5 columns on 1 row):
Instead of defining a 'hardcoded' value for maxcol of 100 the max number of columns can be calculated with distinct_values.
example:
<?variable:maxcol;count(xdoxslt:distinct_values(/FOTOLINKORG1))?>
Labels:
BI
Subscribe to:
Posts (Atom)