EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Software Development Software Development Tutorials PL/SQL Tutorial PLSQL pivot
 

PLSQL pivot

Updated April 5, 2023

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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

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

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Software Development Course

Web development, programming languages, Software testing & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Limited Time Offer! - ENROLL NOW