Скачать или смотреть Transpose Rows to Columns with the PIVOT Function in Oracle SQL

Learn how to efficiently use the `PIVOT` function in Oracle SQL to transpose rows into columns, allowing for better data presentation and analysis.
---
This video is based on the question https://stackoverflow.com/q/64390380/ asked by the user 'AJ Zacharia' ( https://stackoverflow.com/u/12503033/ ) and on the answer https://stackoverflow.com/a/64390738/ provided by the user 'Marmite Bomber' ( https://stackoverflow.com/u/4808122/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Rows to columns using PIVOT function (Oracle)

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Transpose Rows to Columns with the PIVOT Function in Oracle SQL

If you're working with data in Oracle SQL, you may come across a situation where you need to transpose rows into columns for better clarity and usability. This is particularly useful when you're dealing with data that has multiple entries for the same key, such as grades for different subjects by a student. In this guide, we will explore how to use the PIVOT function to achieve this.

Understanding the Problem

Imagine you have a table that displays students' grades for various subjects. The data might look something like this:

[[See Video to Reveal this Text or Code Snippet]]

You want to transform this table into a format where each subject appears as a column, and all grades for a student are displayed in those columns. Your expected output would be:

[[See Video to Reveal this Text or Code Snippet]]

Here, instead of having multiple rows for the same student and subject, each subject is a distinct column with all associated grades listed together.

The Proposed Solution

Step 1: The Original Query

You may initially attempt to achieve this with a SQL query similar to the following:

[[See Video to Reveal this Text or Code Snippet]]

While this query will work to get distinct grades for each subject, it will not return the desired aggregated results as you want all grades listed, not just the maximum grade.

Step 2: Modify the Aggregate Function

To get all grades for each subject aggregated into a single string, you need to replace the MAX function with the LISTAGG function. The LISTAGG function concatenates values while allowing you to specify a delimiter, which is ideal for our need to combine multiple grades into one field.

Here is the revised query:

[[See Video to Reveal this Text or Code Snippet]]

Step 3: Review the Expected Output

When you run the updated query, you should expect to see the following output:

[[See Video to Reveal this Text or Code Snippet]]

This output now correctly displays the grades for each subject in a consolidated format.

Conclusion

Using the PIVOT function in Oracle SQL is a powerful way to transpose rows into columns and present your data in a more readable format. The key to achieving the desired result in this case is switching from the MAX aggregate function to the LISTAGG function, allowing for the combination of multiple entries into single cells. This approach not only improves clarity but also aids in data analysis, making it easier to understand student performance across subjects. Whether you're dealing with educational data or similar datasets, mastering the PIVOT function can significantly enhance your SQL querying capabilities.

Now that you know how to transpose rows to columns in Oracle SQL, give it a try with your own datasets and see how it helps you uncover insights!