I was very pleased when I heard about Oracle adding pivot
functionality in select statements. Finally — we wouldn’t have to
copy the data to a spreadsheet or code a ton of sum(case
when col1 = 'X' then amount else 0 end) total_X for each
column we would want to display.
I am basically looking for three things in a pivot-style query:
- the ability to specify which column will be pivoted as one or more columns in the resulting query
- row subtotals
- column subtotals
The first item is the only one that really matters. I can work around the other two, so let’s get started.
Create a test table with a few rows:
create table bob_pivot_test( id number, cost number, tx_date date, product_type varchar2(50)); insert into bob_pivot_test values (1,10.99,sysdate-10,'Toy'); insert into bob_pivot_test values (1,10.99,sysdate-9,'Toy'); insert into bob_pivot_test values …[Read more]