PL / SQL : How to Pivot data in the oracle table.

When we have data in row based we have to convert it into column base. for that we can use pivot machenaism in PL/SQL. in this post i’m going to explain how to pivot data frm oracle databse table and insert pivoted data to another table.


Step 1 : Create table and insert data using below scripts which contains the data to pivot.
Table Creation Script:
CREATE TABLE Products
( Product_id integer NOT NULL,
  customer_Name varchar2(50) NOT NULL,
  order_id integer,
  CONSTRAINT products_pk PRIMARY KEY (Product_id)
);
Insert Statements:
insert into Products values (0001,'Pubudu',10);
insert into Products values (0002,'Pubudu',20);
insert into Products values (0002,'Pubudu',30);
insert into Products values (0003,'Dewagama',30);
insert into Products values (0004,'Dewagama',40);
insert into Products values (0005,'Chamari',10);
insert into Products values (0006,'Chamari',10);
insert into Products values (0007,'Chamari',10);
insert into Products values (0008,'Chamari',10);
insert into Products values (0009,'Chamari',20);
insert into Products values (0010,'Fonseka',20);
insert into Products values (0011,'Fonseka',10);
insert into Products values (0012,'Fonseka',20);
Step 3 : When we executing below script, the PIVOT clause would return the following results:
SELECT * FROM
(
  SELECT customer_Name, order_id
  FROM Products
)
PIVOT
(
  COUNT(order_id)
  FOR order_id IN (10, 20, 30, 40)
)
ORDER BY customer_Name;
Output :

Step 4 : If you need to store the above result into table you can execute below statement. then you can see the pivoted data in the newly created table.
create table Products_pivot as
SELECT * FROM
(SELECT customer_Name, order_id FROM Products)
PIVOT(COUNT(order_id)FOR order_id IN (10, 20, 30, 40 ))
ORDER BY customer_Name;

once you executed above sql code, Data you can see as below.