Oracle pivot & unpivot

pivot & unpivot 11g New features

1 pivot

Appears as column-value pairs, typical of row-to-column report functions.

create table test_demo (id int,name varchar(20), numbers int); ---Create table insert into test_demo values (1, 'apple', 1000);insert into test_demo values (2, 'Apple', 2000);insert into test_demo values (3, 'apple', 4000);insert into test_demo values (4, 'tangerine', 5000);insert into test_demo values (5, 'tangerine', 3000);insert into test_demo values (6, 'Grape', 3500);insert into test_demo values (7, 'Mangguo', 4200);insert into test_demo values(8, ' Mangguo', 5500);commit; select name, sum(numbers) from test_demo group by name; select * from (select name, numbers from test_demo)pivot(sum(numbers) for name in ('apple ', ' tangerine','grape',' mango'); SQL> select * 2 from (select name, nums from test_demo) 3 pivot(sum(nums) 4 for name in ('apple ' as' apple','orange',' grape','mango'); --alias usage Apple 'Orange' 'grape' 'Mango'----------------------------------------- 7000 8000 3500 9700

Here's the grammar:

pivot aggregate function for column name in type, where in can specify alias, in can also specify subquery, such as select distinct code from customers

2 unpivot

Typical Column Transformation Report Functions

create table Fruit(id int,name varchar(20), Q1 int, Q2 int, Q3 int,Q4 int); where Q1 int, Q2int, Q3 int, Q4 int represent the fourth quarter. insert into Fruit values (1,'apple', 1000,2000,3300,5000);insert into Fruit values (2,'tangerine', 3000,3000,3200,1500);insert into Fruit values (3,'Xiangjia ',2500,3500,2200,2500);insert into Fruit values(4,'Pu',1500,2500,1200,3500);commit;select * from Fruit; select id , name, quarter, sell from Fruit unpivot (sell for quarterin (q1, q2, q3, q4));

Note: unpivot has no aggregate function, and the quarter and sell fields are temporary variables.

Here sell is a statistic, quarter is a quarter and type.

Implementation results:

SQL> select id , name, quarter, sell from Fruit unpivot (sell forquarter in (q1, q2, q3, q4)); ID NAME QUARTER SELL--------------------------------------- -------------------- ---------------------------------------------- 1 apple Q1 1000 1 apple Q2 2000 1 apple Q3 3300 1 apple Q4 5000 2 oranges. Q1 3000 2 oranges. Q2 3000 2 oranges. Q3 3200 2 oranges. Q4 1500 3 Bananas Q1 2500 3 Bananas Q2 3500 3 Bananas Q3 2200 3 Bananas Q4 2500 4 grapes Q1 1500 4 grapes Q2 2500 4 grapes Q3 1200 4 grapes Q4 3500