GMz @ KMz: [Oracle] Pivot Table

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 (1,20.999,sysdate-9,'Toy');

insert into bob_pivot_test values (1,20.999,sysdate-9,'Tool');

insert into bob_pivot_test values (1,20.999,sysdate-9,'Furniture');

insert into bob_pivot_test values (1,20.999,sysdate-9,'Food');

insert into bob_pivot_test values (1,5.00,sysdate-8,'Toy');

insert into bob_pivot_test values (1,5.00,sysdate-8,'Tool');

insert into bob_pivot_test values (1,5.00,sysdate-8,'Furniture');

insert into bob_pivot_test values (1,5.00,sysdate-8,'Food');

insert into bob_pivot_test values (1,5.00,sysdate-7,'Toy');

insert into bob_pivot_test values (1,5.00,sysdate-7,'Tool');

insert into bob_pivot_test values (1,5.00,sysdate-7,'Furniture');

insert into bob_pivot_test values (1,5.00,sysdate-7,'Food');

I looked at the syntax diagram and came up with the following query to start:

SELECT * FROM

(SELECT trunc(tx_date,'dd') day , product_type, cost FROM bob_pivot_test)

PIVOT

(SUM(cost) FOR product_type IN ('Toy','Food'));

DAY 'Toy' 'Food'

--------- ---------- ----------

08-SEP-07 31.989 20.999

09-SEP-07 10 10

07-SEP-07 10.99

10-SEP-07 10 10

select ที่อยู่เหนือ PIVOT เป็นแกน Y ส่วน ที่อยู่ใต้เป็นแกน X

GMz @ KMz