Oracle Pivot & Unpivot

Pivot & Unpivot 11g new Features

1     Pivot

in columns - value In the form of a pair, a typical row-to- column report function .

Create table test_demo (Id int,name varchar (), nums 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,  ' orange ',  5000); Insert into test_demo values (5,   ' oranges ',  3000), insert into test_demo values (6,  ' grapes ',  3500); Insert into  test_demo values (7,  ' Mango ',  4200); Insert into test_demo values (8,  ' mango '),  5500); Commit; select name, sum (nums)   from test_demo group by  name; select *  from  (Select name, nums fromtest_demo) pivot (SUM ( nums)    for name in (' Apple ',  ' orange ',  ' grape ',  ' mango ');  sql> select  *  2    from  (Select name, nums from test_demo)   3   Pivot (SUM (nums)   4     for name in (' Apple '  as  ' apple ',  ' Orange ',  ' grapes ',  ' mango ');   --alias use          Apple          ' oranges '         ' grapes '          ' Mango '---------- ---------- ---------- ----------       7000       8000       3500        9700

Here's the syntax:

Pivot Aggregation Functions for Column Name inch type , where the alias can be specified in,and a subquery can be specified in, for example, select distinct code from customers

2     Unpivot

Typical column-changing report functions

CREATE TABLE Fruit (ID int,name varchar (), Q1 int, Q2 int, Q3 int,q4 int), here Q1 int, q2int, Q3 int, Q4 int for four quarter. INSERT into Fruit values (1, ' Apple ', 1000,2000,3300,5000), insert into Fruit values (2, ' orange ', 3000,3000,3200,1500); INSERT INTO Fruit values (3, ' bananas ', 2500,3500,2200,2500), insert into Fruit values (4, ' grapes ', 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 does not have an aggregate function, andthe quarterandsell fields are also temporary variables.

here sell is the statistic value,quarter represents the quarter and the type.

Execution Result:

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  OrangeSub                  q2                                            3000                                        2  oranges                   Q3                                           3200                                       2  oranges                   Q4                                           1500                                       3   Banana                   q1                                           2500                                       3  Banana                   Q2                                           3500                                       3  Banana                   q3                                           2200                                       3  Banana                   Q4                                            2500                                      4  Grape                   Q1                                            1500                                       4  Grapes                   Q2                                           2500                                       4  Grapes                   Q3                                           1200                                       4  grapes                  Q4                                           3500

This article is from the "90SirDB" blog, be sure to keep this source http://90sirdb.blog.51cto.com/8713279/1928115

Oracle Pivot & Unpivot