SQL Pivot Multiple Columns | Multiple column Pivot Example

In my previous article I have given you the idea about the Pivot statement in SQL with multiple real life examples.In this article I would like to give you examples of SQL Pivot Multiple Columns with real life industry examples.The first thing we need to know about is “What exactly the Pivot is?” then will check for some SQL Pivot Multiple Columns examples to get the idea about the pivot.Basically the pivot is used to transpose the rows in to column. There are multiple columns in single table in sql. The pivot is basically used to transpose those multiple columns in to rows.

Pivot in Oracle with syntax and examples :

In this section we will cover the Pivot statement in Oracle and will get idea about the SQL Pivot Multiple Columns with syntax. The pivot statement in oracle used to aggregate your results and convert rows in columns format. You will get better idea about pivot in Oracle in following section which gives you explanation about syntax.

Syntax for Pivot :

The syntax of Pivot statement will be falls in following categories.

Inline Query :

The Inline query contains the columns which needs to be converted in rows.

Aggregate functions :

We need some aggregate functions which will give the idea of aggregations in the query according to business requirements. So the aggregate functions are the part of Pivot syntax.

Order By :

The Ordering is of two types which is ascending and descending. The SQL order by clause gives us the idea of ordering the data which has been pivoted.

Syntax :

SELECT * FROM
(
SELECT colum_to_pivot1, colum_to_pivot2
FROM table_need_to_Pivot
WHERE conditions
)
PIVOT
(
Pivoting_aggregate_function(colum_to_pivot2)
FOR colum_to_pivot2
IN (expression1,expression2….expression’N’) Or subquery
)
ORDER BY expression [ ASC | DESC ];

Syntax Explaination :

In this section I would like to explain the syntax of Pivot statement. The aggregate functions are nothing but the functions which are specifically used for aggregation purpose. The examples of aggregation functions are SUM ,Count,Max,Min,Avg like functions which has only 1 output and multiple inputs.

The list of values of column_to_Pivot2 are used to pivot the values in to headings which gives us the cross tabulation results. We need to use this instead of list of values. If you are using the subquery in pivoting we can use that result of subquery to determine the values for column_to_Pivot2 to Pivot into headings in cross table query results.

Example of Pivot :

In this section I will try to explain the example of Pivot statement. The Pivot is used to transpose the rows into columns .This is used in reporting and creating interactive reports. The Oracle 11G and above version do have this functionality.

Let us take following examples,

With Table1

(

Select Department_name

From

Employee)

Select *

From Table1

PIVOT

(

Count(*)

For

(Department_name)

IN

(‘IT’,’Finance’,’BI’,’Support’)

);

With the above query we will get to know following things ,

We are selecting and counting the department _name in this query.The first query will select the department name and second query counts it using Count aggregate function.

Output :

IT	Finance	BI	Support
4	8	9	0

The above query will give us the number of departments falls in employee table and number of assigned departments for employees.

Here everyone have to have question in mind that if you can count the number of departments by group by clause why to use pivot. There are two reasons of using pivot,

1.The performance with pivot statements much better that group by clause.

2.You can count the department count with count and group by statement but the question is to transpose it. We need to write PL SQL statement to transpose the values. So the much better way is to use pivot statement.

SQL Pivot Multiple Columns :

In this section we can check one example of SQL Pivot Multiple columns in details. You can use the SQL Pivot statement to transpose multiple columns. The syntax is same but the example is bit complex,

Scenario : We need to check out the maximum as well as minimum salary for the employees department wise.

Query :

WITH
   Table1
AS
(
   SELECT
      Department_name,
      JOB_Title,
      Salary
   FROM
      Employee
)
SELECT
   *
FROM
   Table1
PIVOT
(
   MIN(Salary) AS Minimum_Salary,
   MAX(Salary) AS Maximum_Salary
FOR
   (JOB_Title)
IN
   (
      ‘BI Developer’ AS BI_Developer,
      ‘Support Engineer’ AS Support Executive
   )
)
ORDER BY
   Department_Name;

The above statement will give you the maximum as well as minimum salary for the employees.

Output Will be :

Department Name	Minimum Salary	Maximum Salary	Minimum Salary	Maximum Salary
BI Developer	10000	15000
Support Executive			14000	14000

I would like to explain this query step-by-step,

Step 1 : Analysis of query 1 ,

   SELECT
      Department_name,
      JOB_Title,
      Salary
   FROM
      Employee;

The above query will give you information about department with its salary.

Step 2 : Use pivot statement to fetch maximum of salaries with using statement in Step 1.

SELECT
   *
FROM
   Table1
PIVOT
(
   MIN(Salary) AS Minimum_Salary,
   MAX(Salary) AS Maximum_Salary
FOR
   (JOB_Title)

Step 3 :Use of Inline view to fetch job titles,

FOR
   (JOB_Title)
IN
   (
      ‘BI Developer’ AS BI_Developer,
      ‘Support Engineer’ AS Support Executive
   )
)
ORDER BY
   Department_Name

User need to combine 3 SQL queries and make one Pivot statement to fulfill the business requirement. I hope that you will get the idea of Pivot statements as well as SQL Pivot multiple columns in Oracle. The SQL pivot multiple columns will be used in Oracle 11 G and above versions only. I hope you like this article. If you like this article of SQL pivot multiple columns or if you have any concerns with the same kindly comment in comments section.