Thursday, 23 August 2012

Oracle and Pivot

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

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"))

The results is a table with the columns weg and the individual days

ROAD      MA_AVG    MA_SUM     DI_AVG   .....
XX           XX               XX                 XX 

No comments:

Post a Comment