# 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 Costsai soap 20.00ram shampoo 52.00sai paste 34.00sai brush 10.00kumar soap 22.00ram talc 84.00kumar talc 56.00saran oil 45.00kumar oil 38.00ram 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)sourcePIVOT(sum(Cost) for Product in(soap, shampoo, paste, brush, talc, oil)) pvtorder by Name`

### Output:

`Name soap shampoo paste brush talc oilkumar 22.00 NULL NULL NULL 56.00 38.00ram NULL 107.00 NULL NULL 84.00 NULLsai 20.00 NULL 34.00 10.00 NULL NULLsaran 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, saranfrom (select Name, Product,Cost from shop)sourcePIVOT(sum(Cost) for Name in(sai, ram, kumar, saran))ptorder by Product`

### Output:

`Product sai ram kumar saranbrush 10.00 NULL NULL NULLoil NULL NULL 38.00 45.00paste 34.00 NULL NULL NULLshampoo NULL 107.00 NULL NULLsoap 20.00 NULL 22.00 NULLtalc NULL 84.00 56.00 NULL`

### Unpivot table order by Product:

This will give the original table
`select Name, Product, Costfrom (select Product, sai, ram, kumar, saranfrom (select Name, Product,Cost from shop)sourcePIVOT(sum(Cost) for Name in(sai, ram, kumar, saran))pt)pUNPIVOT(Cost for Name in ( sai, ram, kumar, saran))upvt`

### Output:

`Name Product Costsai brush 10.00kumar oil 38.00saran oil 45.00sai paste 34.00ram shampoo 107.00sai soap 20.00kumar soap 22.00ram talc 84.00kumar talc 56.00`