Home > oracle_11g_new_features > pivot in oracle 11g

pivot in oracle 11g

Pivot function is very important function in some interface projects and dataware house environments.

consider following pivot_test table for this exercise.

create table pivot_test
(customer_id number(10),
customer_category varchar2(10),
customer_account number(10)
)

insert into pivot_test values(1,’MED’,5000);
insert into pivot_test values(1,’RX’,5001);
insert into pivot_test values(1,’CAP’,5002);
insert into pivot_test values(1,’REV’,5003);
commit;

SQL> select * from pivot_test;
CUSTOMER_ID|CUSTOMER_C|CUSTOMER_ACCOUNT
———–|———-|—————-
1|MED       |            5000
1|RX        |            5001
1|CAP       |            5002
1|REV       |            5003

SQL> select *
from
(select CUSTOMER_ID
, CUSTOMER_CATEGORY
, CUSTOMER_ACCOUNT
from pivot_test ) s
PIVOT (sum(CUSTOMER_ACCOUNT)
for CUSTOMER_CATEGORY in (‘MED’,’RX’,’CAP’,’REV’)
)

CUSTOMER_ID|   ‘MED’|    ‘RX’|   ‘CAP’|   ‘REV’
———–|——–|——–|——–|——–
1|    5000|    5001|    5002|    5003

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: