Selected columns and implicit group by
The pivot clause does not require an explicit
group by clause to aggregate the values of the expressions in the specified
aggregate-functions. Rather, the pivot clause implicitly groups on all
columns that are not explicitly mentioned in the pivot clause.
The following table and select statements try to elaborate on that.
create table tq84_pivot_test (
id number,
col_1 varchar2(5),
col_2 varchar2(5),
col_3 varchar2(5),
val_1 number,
val_2 varchar2(5)
);
begin
insert into tq84_pivot_test values (1, 'A', 'X', 'p', 8, 'one' );
insert into tq84_pivot_test values (2, 'A', 'X', 'p', 9, 'two' );
insert into tq84_pivot_test values (3, 'A', 'X', 'q', 2, 'three');
insert into tq84_pivot_test values (4, 'B', 'X', 'p', 7, 'four' );
insert into tq84_pivot_test values (5, 'B', 'X', 'q', 5, 'five' );
insert into tq84_pivot_test values (6, 'B', 'Y', 'r', 4, 'six' );
insert into tq84_pivot_test values (7, 'B', 'Y', 'q', 3, 'seven');
insert into tq84_pivot_test values (8, 'C', 'Z', 'q', 6, 'eight');
insert into tq84_pivot_test values (7, 'A', 'Y', 'q', 1, 'seven');
commit;
end;
/
In the following query, the
pivot clause refers to the
columns val_1 and
col_1. These columns don't appear in the result set as ordinary columns, rather the
in clause specifies the names of the additional columns, the aggregate function the column that is aggregated: The
select
*
from
tq84_pivot_test
pivot (
max(val_1) for
col_1 in ('A', 'B')
)
;
--
-- ID COL_2 COL_3 VAL_2 'A' 'B'
-- ---------- ----- ----- ----- ---------- ----------
-- 1 X p one 8
-- 2 X p two 9
-- 3 X q three 2
-- 4 X p four 7
-- 5 X q five 5
-- 6 Y r six 4
-- 7 Y q seven 1 3
-- 8 Z q eight
The previous select statement is a bit meaningless because it groups on all available columns. Therefore, in most cases, it is advisable to select the columns which are grouped by in a
subquery:
select
*
from (
--
-- This subquery explicitely selects the values
-- we want to have in the result. I. e.: the values for the columns ID and
-- VAL_2 are explicitely excluded:
--
select
col_1, col_2, col_3,
val_1
from
tq84_pivot_test
)
pivot ( max(val_1) for col_1 in ('A', 'B'))
;
--
-- COL_2 COL_3 'A' 'B'
-- ----- ----- ---------- ----------
-- X p 9 7
-- X q 2 5
-- Y r 4
-- Y q 1 3
-- Z q
Finally, it is also possible to specify combination of «column values» for the additional columns.
By default, they have a rather ugly name with apostrophes in them. They can, however, be given more beautiful or meaningful names using the as … clause:
select
*
from (
select
col_1, col_2, col_3,
val_1
from
tq84_pivot_test
)
pivot (
max(val_1) for
(col_1, col_2) in (
('A', 'X') as ax, -- Make column name more pleasing
('B', 'Y'),
('C', 'Z')
)
);
--
-- COL_3 AX 'B'_'Y' 'C'_'Z'
-- ----- ---------- ---------- ----------
-- p 9
-- q 2 3 6
-- r 4
Cleaning up:
drop table tq84_pivot_test;
Other examples
Sum of sale per month and item
A table that stores the date (dt) when an item (identified by txt) was sold. The price is kept in value:
create table tq84_sale (
dt date,
txt varchar2(10),
value number(5,2)
);
Fill some test data:
begin
-- M o n t h
--
-- 07 08 09
-- ---------------
insert into tq84_sale values (date '2000-08-10', 'Item 1', 10); -- 10
insert into tq84_sale values (date '2000-09-12', 'Item 1', 12); -- 12
insert into tq84_sale values (date '2000-09-15', 'Item 1', 14); -- 14
insert into tq84_sale values (date '2000-09-16', 'Item 1', 2); -- 2
-- -- -- --
-- Sums for Item 1 -- 10 28
-- == == ==
insert into tq84_sale values (date '2000-07-02', 'Item 2', 11); -- 11
insert into tq84_sale values (date '2000-07-05', 'Item 2', 7); -- 7
insert into tq84_sale values (date '2000-08-22', 'Item 2', 8); -- 8
-- -- -- --
-- Sums for Item 2 : 18 8
-- == == ==
insert into tq84_sale values (date '2000-08-22', 'Item 3', 6); -- 6
-- -- -- --
-- Sums for Item 2 : 6
-- == == ==
end;
/
Query the data for three select months:
with translate_dt_to_month as (
select
to_char(dt, 'MM') month,
txt,
value
from
tq84_sale
)
select
*
from
translate_dt_to_month
pivot (
sum(value) for
month in ('07' jul, '08' aug, '09' sep)
)
order by
txt;
--
-- TXT JUL AUG SEP
-- ---------- ---------- ---------- ----------
-- Item 1 10 28
-- Item 2 18 8
-- Item 3 6
Cleaning up:
drop table tq84_sale
Summing two values
This example is similar to the previous one, but is sums two values per period and item:
create table tq84_pivot (
dt date,
txt varchar2(10),
value1 number(5,2),
value2 number(5,2)
);
insert into tq84_pivot values (date '2000-08-10', 'Item 1', 10, 9);
insert into tq84_pivot values (date '2000-09-12', 'Item 1', 12, 11);
insert into tq84_pivot values (date '2000-09-15', 'Item 1', 14, 13);
insert into tq84_pivot values (date '2000-09-16', 'Item 1', 2, 1);
insert into tq84_pivot values (date '2000-07-02', 'Item 2', 11, 10);
insert into tq84_pivot values (date '2000-07-05', 'Item 2', 7, 6);
insert into tq84_pivot values (date '2000-08-22', 'Item 2', 8, 7);
insert into tq84_pivot values (date '2000-08-22', 'Item 3', 6, 5);
insert into tq84_pivot values (date '2000-09-23', 'Item 3',1000, 5);
with translate_dt_to_month as (
select to_char(dt, 'MM') month, txt, value1, value2 from tq84_pivot
)
select * from translate_dt_to_month
--
pivot ( sum(value1) sum_1, sum(value2) sum_2 for month in ('07', '08', '09') )
--
order by txt;
--
-- TXT '07'_SUM_1 '07'_SUM_2 '08'_SUM_1 '08'_SUM_2 '09'_SUM_1 '09'_SUM_2
-- ---------- ---------- ---------- ---------- ---------- ---------- ----------
-- Item 1 10 9 28 25
-- Item 2 18 16 8 7
-- Item 3 6 5
drop table tq84_pivot;
«Better» aliases for generated pivot columns.
create table tq84_pivot_03 (
nm number,
tx varchar2(2)
);
insert into tq84_pivot_03 values ( 1, '/?' );
insert into tq84_pivot_03 values ( 3, '/?' );
insert into tq84_pivot_03 values ( 7, ' ' );
insert into tq84_pivot_03 values ( 8, ' ' );
insert into tq84_pivot_03 values ( 2, ' ' );
insert into tq84_pivot_03 values (66, chr(10));
insert into tq84_pivot_03 values (59, chr(10));
insert into tq84_pivot_03 values (31, chr(10));
select "'/?'", "' '", "'
'"
from tq84_pivot_03
pivot (max(nm) for tx in ('/?', ' ', '
'));
select
slash_question,
space_space,
new_line
from
tq84_pivot_03
pivot (max(nm) for tx in (
'/?' as slash_question,
' ' as space_space, '
' as new_line
)
);
drop table tq84_pivot_03 purge;
Two aggregates
create table tq84_two_aggregates (
item varchar2(20) not null,
region varchar2( 5) not null check (region in ('North', 'South', 'West', 'East')),
price number,
sold number,
foo varchar2(2)
);
insert into tq84_two_aggregates values ('Apple', 'North', 0.85, 1094, '?' );
insert into tq84_two_aggregates values ('Apple', 'South', 0.79, 88, '!!');
insert into tq84_two_aggregates values ('Apple', 'West' , 0.81, 203, '%' );
insert into tq84_two_aggregates values ('Pear' , 'North', 1.05, 4593, '#-');
insert into tq84_two_aggregates values ('Pear' , 'South', 0.98, 5010, '/' );
insert into tq84_two_aggregates values ('Pear' , 'East' , 1.03, 912, '\\');
insert into tq84_two_aggregates values ('Mango', 'South', 2.44, 417, '_/');
-- update tq84_two_aggregates set foo = '1';
select * from (
select item, price, sold, /* foo,*/ region from tq84_two_aggregates
)
pivot (
sum(sold) as sold ,
max(price) as price
--
for region in ('North' as north_, 'South' as south_, 'West' as west_, 'East' as east_)
)
;
drop table tq84_two_aggregates purge;