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