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.00

Pivot 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 Name

Output:



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.00

Pivot 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 Product

Output:



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 NULL

Unpivot 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))upvt

Output:



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


Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: