DanBrother的部落格

-----------------------------------------------------------------------------------
-- Convert Rows into Columns using Oracle PIVOT
-----------------------------------------------------------------------------------
Example:

create table fruit_purchase_list
(day varchar2(10),
fruit varchar2(30),
constraint pk_fruit_purchase_list primary key(day,fruit)
);

insert into fruit_purchase_list
values('2014-01-01','APPLE');

insert into fruit_purchase_list
values('2014-01-01','STRAWBERRY');

insert into fruit_purchase_list
values('2014-01-01','PINEAPPLE');

insert into fruit_purchase_list
values('2014-01-01','BANANA');

insert into fruit_purchase_list
values('2014-01-01','PEACH');

insert into fruit_purchase_list
values('2014-01-01','GUAVA');
commit;

col fruit format a14
select day,fruit from fruit_purchase_list;
>>
DAY                  FRUIT
---------------  --------------
2014-01-01   APPLE
2014-01-01   STRAWBERRY
2014-01-01   PINEAPPLE
2014-01-01   BANANA
2014-01-01   PEACH
2014-01-01   GUAVA

select *
from fruit_purchase_list
pivot
(
  count(fruit)
  for fruit in (
                   'APPLE' as APPLE,
                   'ORANGE' as ORANGE,
                   'BANANA' as BANANA,
                   'STRAWBERRY' as STRAWBERRY,
                   'GRAPE' as GRAPE,
                   'GUAVA' as GUAVA,
                   'PINEAPPLE' as PINAPPLE,
                   'PEACH' as PEACH
                )
)
where day = '2014-01-01';
>>
DAY                 APPLE ORANGE BANANA STRAWBERRY GRAPE    GUAVA   PINAPPLE   PEACH
---------------  --------- ---------- ---------- ----------------- ---------- ---------- -------------  ---------
2014-01-01         1         0          1              1                   0         1              1               1

-----------------------------------------------------------------------------------
-- Convert Columns into a Single Row using Oracle UNPIVOT
-----------------------------------------------------------------------------------

Example:
create table fruit_purchase_list2
(day varchar2(10),
apple number(1),
orange number(1),
banana number(1),
strawberry number(1),
grape number(1),
guava number(1),
pineapple number(1),
peach number(1)
);

insert into fruit_purchase_list2
values('2014-01-01',1,0,1,1,0,1,1,1);

insert into fruit_purchase_list2
values('2014-01-08',0,1,0,1,1,1,0,0);

col day format a10
select * from fruit_purchase_list2;
>>
DAY                    APPLE    ORANGE BANANA STRAWBERRY GRAPE   GUAVA   PINEAPPLE  PEACH
---------------      ---------- ------------ ---------- ---------------- ---------- ---------- --------------   ----------
2014-01-01            1            0             1              1                0            1           1                  1
2014-01-08            0            1             0              1                1            1           0                  0

col fruit format a14
select day,fruit,picked
from fruit_purchase_list2
unpivot
(
   picked for fruit in (APPLE,ORANGE,BANANA,STRAWBERRY,GRAPE,GUAVA,PINEAPPLE,PEACH)
)
where day = '2014-01-01' order by picked desc;
>>
DAY                FRUIT              PICKED
---------------- ------------------- ----------
2014-01-01   APPLE                 1
2014-01-01   STRAWBERRY      1
2014-01-01   PINEAPPLE         1
2014-01-01   BANANA              1
2014-01-01   PEACH                1
2014-01-01   GUAVA                1
2014-01-01   GRAPE                0
2014-01-01   ORANGE             0