Oracle Vs MS-SQL : PIVOT

Pivot function exists both in Oracle as well as MS-SQL. It is used to convert column result into rows for the specific values. There is not much difference between Oracle and MS-SQL. It can be better explained with the help of example.


Let’s create a table called ProductSales which stores the information about sales person, product and the sales amount.


create table ProductSales
(
         salesperson   varchar(50),
         Product        varchar(20),
         salesAmount decimal(14,4)
)

Insert the below values

insert into ProductSales values ('Jithesh','Pickles',100)
insert into ProductSales values ('Jithesh','Pickles',25)
insert into ProductSales values ('Jithesh','Oranges',300)
insert into ProductSales values ('kb','Oranges',50)
insert into ProductSales values ('kn','Oranges',500)
insert into ProductSales values ('kn','Pickles',44)



Table looks like

salesperson                                   Product                            salesAmount
-----------------------------  --------------------     -------------------------
Jithesh                                      Pickles                       100.0000
Jithesh                                      Pickles                         25.0000
Jithesh                                      Oranges                     300.0000
kb                                            Oranges                       50.0000
kn                                            Oranges                      500.0000
kn                                            Pickles                          44.0000

Now I want to display the sales amount for the product Pickles and Oranges in a row instead of columns for the sales persons and so for one sales person, there will be only one row.

We can use PIVOT function to achieve this.

SELECT SalesPerson, [Oranges] AS Oranges, [Pickles] AS Pickles
FROM
(SELECT SalesPerson, Product, SalesAmount
FROM ProductSales ) ps
PIVOT
(
SUM (SalesAmount)
FOR Product IN
( [Oranges], [Pickles])
) AS pvt

For the above query there are four parts.

1. SELECT SalesPerson, [Oranges] AS Oranges, [Pickles] AS Pickles FROM
2. (SELECT SalesPerson, Product, SalesAmount FROM ProductSales ) ps
3. PIVOT
    (
        SUM (SalesAmount)
4. FOR Product IN
    ( [Oranges], [Pickles])
   ) AS pvt



Here the columns mentioned in the 1 and the 4th are fixed which is actually the value of the field Product. This query can calculate sales amount for the product values mentioned as a column and the balance it will ignore. Ultimately if you want to convert the values of column into row we need to mention it explicitly and it is fixed. Those values which are not mentioned as columns will be ignored in the result set.