How to 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.