How to create a PIVOT table in Oracle.

Create a Pivot Table in Oracle

First create a PLANNING table in oracle using following script.

CREATE TABLE PLANNING (CITY VARCHAR2(25), TYPEOFCITY VARCHAR2(20), 
              CONSUMERS NUMBER(20), SURVEYEDCONSUMERS NUMBER(20));

Now insert some dummy records in the table.

insert into PLANNING values ('AJMER','URBAN',85589,45836);
insert into PLANNING  values ('AJMER','RURAL',13853,8328);
insert into PLANNING values ('BHILWARA','URBAN',54149,27327);
insert into PLANNING values ('BHILWARA','RURAL',8384,4331);
insert into PLANNING values ('NAGOR','URBAN',49752,43919);
insert into PLANNING values ('NAGOR','RURAL',8711,3645);
insert into PLANNING values ('SIKAR','URBAN',49802,45283);
insert into PLANNING values ('SIKAR','RURAL',8860,3107);

commit;

Check the records in PLANNING table before creating PIVOT table in Oracle.

select * from planning;

Ruin the following script to create PIVOT table.

select row_number() over (order by CITY) as SEQ_ID,CITY
,sum("URBAN_CONSUMERS") as "URBAN_CONSUMERS"
,sum("RURAL_CONSUMERS") as "RURAL_CONSUMERS"
,sum("URBAN_SURVEYEDCONSUMERS") as "URBAN_SURVEYED_CON"
, sum("RURAL_SURVEYEDCONSUMERS") as "RURAL_SURVEYED_CON",
sum("URBAN_CONSUMERS") - sum("URBAN_SURVEYEDCONSUMERS")  as "PEN_URBAN_SUR_CON",
sum("RURAL_CONSUMERS") - sum("RURAL_SURVEYEDCONSUMERS") as "PEN_RURAL_SUR_CON"
from
(SELECT CITY,
CASE WHEN TYPEOFCITY = 'URBAN' then CONSUMERS else 0 end as "URBAN_CONSUMERS",
CASE WHEN TYPEOFCITY = 'RURAL' then CONSUMERS else 0 END as "RURAL_CONSUMERS",
CASE when TYPEOFCITY = 'URBAN' then SURVEYEDCONSUMERS else 0 end as "URBAN_SURVEYEDCONSUMERS",
CASE WHEN TYPEOFCITY = 'RURAL' then SURVEYEDCONSUMERS else 0 end as "RURAL_SURVEYEDCONSUMERS"
from PLANNING) group by CITY;

Don’t forget to drop planning table after using the above code.

DROP TABLE PLANNING;

There are many options to create a PIVOT table in Oracle.

You Must Read:

Transpose Rows to Columns/Columns to Rows in Oracle

If you have any query/suggestion, feel free to comment below the post.

SiteGround Black Friday Sale Below Banner

Get Free Blogging Tips & Technology updates in your Email !!!


Subscribe via RSS feed

The following two tabs change content below.
Anil Kumar
IT Blogger Tips focus on Blogging Tips, SEO Tips, Social Media, SQL Tips, PL/SQL Tips, Oracle DBA, Linux/Unix, Latest Technology, How Tos and Technical Solutions. You can find us on Facebook | Twitter |
Find on Google+

  • Anonymous

    Thanks it helped me…

  • selvamptl

    nice it’s help full for my class

Siteground SALE
70% OFF SiteGround WordPress Hosting – $2.95/month (Exclusive Offer !!!)