Pivot and Unpivot relational operators
In this article i explain about the concept of using pivot and unpivot relational operators to change a table-valued expression into another table. To convert one column in the table into multiple columns in the output PIVOT concept is used.
PIVOT rotates unique values from one column in the table into multiple columns in the output.
UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.
Let us consider one table
create table shop (Name varchar(10), Product varchar(10), Cost decimal(6,2))
insert into shop values ('sai','soap',20), ('ram','shampoo',52), ('sai','paste',34),
('sai','brush',10), ('kumar','soap',22), ('ram','talc',84),
('kumar','talc',56), ('saran','oil',45),
('kumar','oil',38), ('ram','shampoo',55)Output for the table:
Name Product Cost
sai soap 20.00
ram shampoo 52.00
sai paste 34.00
sai brush 10.00
kumar soap 22.00
ram talc 84.00
kumar talc 56.00
saran oil 45.00
kumar oil 38.00
ram shampoo 55.00Pivot table order by Name:
Products in the column 'Product' as made as a column name for the pivot table and cost is given as the value for each name.
select Name, soap, shampoo, paste, brush, talc, oil
from (select Name, Product, Cost from shop)source
PIVOT(sum(Cost) for Product in(soap, shampoo, paste, brush, talc, oil)) pvt
order by NameOutput:
Name soap shampoo paste brush talc oil
kumar 22.00 NULL NULL NULL 56.00 38.00
ram NULL 107.00 NULL NULL 84.00 NULL
sai 20.00 NULL 34.00 10.00 NULL NULL
saran NULL NULL NULL NULL NULL 45.00Pivot table order by Product:
Names in the column 'Name' as made as a column name for the pivot table and cost is given as the value for each product.
select Product, sai, ram, kumar, saran
from (select Name, Product,Cost from shop)source
PIVOT(sum(Cost) for Name in(sai, ram, kumar, saran))pt
order by ProductOutput:
Product sai ram kumar saran
brush 10.00 NULL NULL NULL
oil NULL NULL 38.00 45.00
paste 34.00 NULL NULL NULL
shampoo NULL 107.00 NULL NULL
soap 20.00 NULL 22.00 NULL
talc NULL 84.00 56.00 NULLUnpivot table order by Product:
This will give the original table
select Name, Product, Cost
from
(
select Product, sai, ram, kumar, saran
from (select Name, Product,Cost from shop)source
PIVOT(sum(Cost) for Name in(sai, ram, kumar, saran))pt
)p
UNPIVOT(Cost for Name in ( sai, ram, kumar, saran))upvtOutput:
Name Product Cost
sai brush 10.00
kumar oil 38.00
saran oil 45.00
sai paste 34.00
ram shampoo 107.00
sai soap 20.00
kumar soap 22.00
ram talc 84.00
kumar talc 56.00