EDUCBA

Home Software Development Software Development Tutorials PL/SQL Tutorial PLSQL pivot

PLSQL pivot

PLSQL pivot

Introduction to PLSQL pivot

PL/SQL provides the different types of functionality to the user; the pivot is the one type of functionality that is provided by the PL/SQL. Basically, we call it a pivot clause. By using pivot clauses, we cross table query as per requirement; in another way, we can combine, or we can aggregate our result from rows into the columns as per our requirement. Basically, the pivot clause introduced in Oracle 11g and the pivot returns more than one column after the execution. By using pivot clauses, we can combine the difference into a single result and generate the required output.

Syntax

select * from (select colm name 1, colm name name N from specified table where conditional expression)
PIVOT
(specified aggregate function name(colm name ) from colm name 2 IN (specified expression 1, specified expression 2,…..specified expression N) | specified sub query)
Order by specified expression [ASC | DESC];

Explanation

Start Your Free Software Development Course

Web development, programming languages, Software testing & others

In the above example, we use different parameters as follows.

First, we use the select clause to retrieve the records from the specified table. Inside the select, we write the subquery with a column name, and here we specify where clause with conditional expression.

Specified aggregate function name: it is used to specify the aggregate function name that we can write any function name such as SUM, MIN, MAX, etc.

IN (specified expression 1, specified expression 2,…..specified expression N): It is used to hold the list of column name 2 that values to pivot into the cross table.

Specified subquery: Basically, it is used instead of list values, and the output of the subquery would be utilized to calculate the values for column 2 in the cross-tabulation query output, which would then be translated to headings in this syntax.

How does pivot work in PL/SQL?

Now let’s see how pivot clauses work in PL/SQL as follows.

Let’s see the different ways to implement the pivot clause as follows.

Specify the Group Columns:

Any columns not stated in the FOR clause are utilized as a part of the Oracle PIVOT GROUP BY when employing the PIVOT keyword. The only column in the previous example was the location column, which was fine.

We can also use the Where clause with pivot clause:

The results of our searches above provide a pivoted summary of all data. A few fields are used to aggregate all of the entries, and the SUM of the selling amount is displayed.

What if you want to limit it to just a few rows?

Like a regular SELECT query, you may use a WHERE clause. Then we will get the error due to incorrect syntax, so we need to write the correct syntax that means the PIVOT clause must appear after the WHERE clause; this is the case.

Now let’s see how we can use Aliasing in the pivot column:

The column headings will be shown as the table’s column name in the queries we’ve looked at so far. What if you want to call them something else? A column alias can be specified using the PIVOT keyword. Both the pivot clause and the pivot in a clause can be used for this.

Now let’s see how we can perform the multiple aggregations in pivot:

In the above ways, we implemented single aggregation on a single specified column. But Oracle provides the functionality to add more than one aggregate function with pivot query that means we can perform the SUM and COUNT aggregate function.

We can make the group of multiple columns:

This is another way to implement the multiple columns into the pivot statement to group by multiple columns as per our requirement.

We can implement pivot with XML as follows:

You may display your findings in an XML format using the PIVOT keyword. It’s as simple as following the PIVOT keyword with the XML keyword.

Examples of PLSQL pivot

Now let’s see the different examples of pivot clauses in PL/SQL for better understanding as follows.

First, we need to create the table by using the following create table statement as follows.

create table stud(roll_no integer not null, name varchar2(50), dept_id integer not null, primary key(roll_no));

Explanation

By using create table statement, we created a new table name as a stud with different attributes such as roll_no, name, and dept_id with different data types, and in this example, the primary key is roll_no. The final output of the above statement we illustrated by using the following screenshot as follows.

output 1

Now insert some records to perform the pivot clause by using the following insert into statements as follows.

insert into stud(roll_no, name, dept_id) values(1,'Jenny',10);
insert into stud(roll_no, name, dept_id) values(2,'Jenny',10);
insert into stud(roll_no, name, dept_id) values(3,'Jenny',20);
insert into stud(roll_no, name, dept_id) values(5,'Sameer',20);
insert into stud(roll_no, name, dept_id) values(6,'Sameer',10);
insert into stud(roll_no, name, dept_id) values(7,'Sameer',20);

Explanation

In the stud table, we inserted a total of 6 records by using the above statement. The final output of the above statement we illustrated by using the following screenshot as follows.

PLSQL pivot output 2

If records are not in order, then we can use order by clause to make the records in order. In this example, all records we order by roll_no, as shown in the above screenshot.

Now implement the pivot clause that means write the cross table subquery as follows.

select * from (select name, dept_id from stud) pivot (count(dept_id) for dept_id IN (10, 20, 30)) order by name;

Explanation

In the above example, we write the two different queries that we call subquery and merge by using the pivot clause. Now let’s see how it works. In the above example, we first decide which field we want to add in the pivot clause; here, we add name and dept_no. After that, we need to specify the column in any order that we want. The next part of the query, it contains the aggregate function and pivot value that we want, as shown in the above statement. The final output of the above statement we illustrated by using the following screenshot as follows.

PLSQL pivot output 3

Conclusion – PLSQL pivot

We hope from this article you learn PL/SQL pivot. From the above article, we have learned the basic syntax of the pivot, and we also see different examples of the pivot. From this article, we learned how and when we use PL/SQL pivot.

Recommended Articles

We hope that this EDUCBA information on “PLSQL pivot” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. PL/SQL Date Functions
  2. PL/SQL Cursor Loop
  3. PL/SQL TRIM
  4. Triggers in PL/SQL