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

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.

Open Demat Account Online for Free with Indira Securities in just 15 mins.

ReplyDeleteComprehensive brokerage calculator to calculate how much brokerage, STT, tax etc.

ReplyDelete