Oracle SQL PIVOT and UNPIVOT: The Complete Guide (2023)

Have you ever needed to convert a set of data from rows into columns? You can do this using the Oracle PIVOT feature (and the reverse using Oracle UNPIVOT).

Table of Contents

This guide covers the following topics.

• The Problem
• The PIVOT Keyword in Oracle SQL
• Simple PIVOT Example
• Specifying Grouped Columns
• Using the WHERE Clause with PIVOT
• Aliasing PIVOT Columns
• Perform Multiple Aggregations
• Group By Multiple Columns
• PIVOT Example with XML
• Dynamically Specifying Columns
• Transposing Columns into Rows with UNPIVOT
• A Simple UNPIVOT Example
• Handling NULL Values in UNPIVOT
• Aliases and UNPIVOT
• Can You Use Oracle PIVOT Without an Aggregate?

Now let’s get into the guide!

The Problem

Let’s say you’ve got this set of data in a table called cust_sales:

This represents a set of sales for different customers in different regions. What if you wanted to find the SUM of sales for each location and customer? You could write an SQL query using SUM and GROUP BY:

SELECT location, customer_id, SUM(sale_amount)FROM cust_salesGROUP BY location, customer_idORDER BY location, customer_id;

This will show the following results:

What if, instead of showing two columns for the location and customer_id, you wanted to show the locations in each row and the customer_id in each column? You would be looking for a result like this:

This is often called pivoting, or transposing rows and columns, or transposing columns and rows. It can be done in a couple of ways in SQL, and the easiest is to use the Oracle PIVOT keyword.

While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here:

The PIVOT Keyword in Oracle SQL

Oracle has the ability to create a result set that transposes or pivots columns and rows to provide a summary. This is done using the SQL PIVOT keyword. This keyword was introduced in Oracle 11g.

This keyword is applied to a SELECT statement, and looks like this:

SELECT columnsFROM tablesPIVOT [XML] ( pivot_clause, pivot_for_clause, pivot_in_clause);

After the PIVOT keyword, it includes several components:

• XML: This is an optional keyword, and it lets you output data in an XML format. There’s an example of this below.
• pivot_clause: This defines what the query will aggregate the data on, because the PIVOT keyword aggregates data.
• pivot_for_clause: This defines which columns will be grouped and pivoted on.
• pivot_in_clause: This is used to filter the values for the columns in the pivot_for_clause. Each of the values in this clause will be a separate column.

If that’s confusing to you, don’t worry. The examples below will make it much easier to understand.

Simple PIVOT Example

Let’s say you wanted to show the results mentioned earlier: locations in the first column, each customer having a different column, and the SUM of the sale_amount as the values.

The query to show this data in a standard GROUP BY with no pivoting is:

SELECT location,customer_id,SUM(sale_amount)FROM cust_salesGROUP BY location, customer_idORDER BY location, customer_id;

The query to transform this into a pivoted result set looks like this:

SELECT *FROM cust_salesPIVOT ( SUM(sale_amount) FOR customer_id IN (1, 2, 3, 4, 5, 6));

Let’s break this query down:

• SELECT *. We start with SELECT * FROM cust_sales. Normally I don’t like using SELECT *, but if we list the columns individually, then an error will be shown because it’s the PIVOT clause that is supposed to determine what is shown.
• PIVOT: This indicates that we want to use the SQL pivot functionality to display our output. We open the bracket to include a range of parameters.
• SUM(sale_amount): This is the value to be shown in the middle of the output. It’s a number value that has an aggregate function applied.
• FOR customer_id: This is the column in the table that’s used to display different columns in the output. The values are shown as column headers.
• IN (1, 2, 3, 4, 5, 6): These are the values of the FOR column mentioned above that are used in the output. It’s like a WHERE filter. You don’t have to specify all columns, but I have here.

Any columns not mentioned in the FOR clause are displayed as rows and are grouped. In this case, the location field is used.

The output of this query is:

This is the result of the PIVOT keyword. The rows are the different locations, the columns are the customer_ids 1 to 6, and the values are the SUM of sale_amount. Any NULL values are because no data exists. For example, there are no sales in the west location for customer_id 2.

Specifying Grouped Columns

When using the PIVOT keyword, any columns not mentioned in the FOR clause are used as part of the Oracle PIVOT GROUP BY. In the above example, the only column was the location column, which was OK.

However, what if your output included another column, called prod_category?

If you run the same PIVOT query, you’ll get this result. In this example, I’ve used a second table called cust_sales_category and reduced the number of customers..

SELECT *FROM cust_sales_categoryPIVOT ( SUM(sale_amount) FOR customer_id IN (1, 2, 3, 4));

The results are:

Our data is grouped by location and prod_category. This happened because the customer_id was mentioned in the FOR clause, and the statement used all other columns to GROUP BY.

What if we don’t want to group by location and prod_category?

We can try to alter the SELECT query to only select the location column.

SELECT location, customer_id, sale_amountFROM cust_sales_categoryPIVOT ( SUM(sale_amount) FOR customer_id IN (1, 2, 3, 4));

The result of this query is:

ORA-00904: "SALE_AMOUNT": invalid identifier00904. 00000 - "%s: invalid identifier"*Cause:*Action:Error at Line: 87 Column: 31

You get this error because you can’t specify individual columns as part of the SELECT clause. However, there are a couple of ways to do this: a WITH clause or a subquery.

To specify the columns being grouped as part of a PIVOT query, you can use the Oracle PIVOT with a subquery where the subquery only shows the columns you want:

SELECT *FROM ( SELECT location, customer_id, sale_amount FROM cust_sales_category)PIVOT ( SUM(sale_amount) FOR customer_id IN (1, 2, 3, 4));

Notice how instead of selecting directly from the table, I’m performing a SELECT * from a subquery, and the subquery mentions the individual columns. This means the prod_category is ignored, and you get a result like this:

The result is not grouping the prod_category column at all. If you want to group by the prod_category instead of the location, simply change the field in the subquery:

SELECT *FROM ( SELECT prod_category, customer_id, sale_amount FROM cust_sales_category)PIVOT ( SUM(sale_amount) FOR customer_id IN (1, 2, 3, 4));

So that’s how you can change the columns being displayed and being grouped by using a PIVOT query: by using Oracle PIVOT with a subquery.

Using the WHERE Clause with PIVOT

In our queries above, the results show the pivoted summary of all data. All records are grouped by a few fields, and the SUM of the sale amount is shown.

What if you wanted to restrict this to only certain rows?

You can use a WHERE clause, just like a normal SELECT query.

However, if you put the WHERE clause in the middle, you’ll get an error:

SELECT *FROM ( SELECT location, customer_id, sale_amount FROM cust_sales_category)WHERE location <> 'south'PIVOT ( SUM(sale_amount) FOR customer_id IN (1, 2, 3, 4));

ORA-00933: SQL command not properly ended00933.00000 - "SQL command not properly ended"*Cause:*Action:Error at Line: 127 Column: 1

This is because the WHERE clause must come after the PIVOT clause. A correct query would look like this:

SELECT *FROM ( SELECT location, customer_id, sale_amount FROM cust_sales_category)PIVOT ( SUM(sale_amount) FOR customer_id IN (1, 2, 3, 4))WHERE location <> 'south';

The WHERE clause is at the end of the query, after the SQL PIVOT clause. This shows the following results:

The results exclude the records where the location is equal to ‘south’.

If you’re using the subquery method to determine columns, you can also put the WHERE clause inside the subquery:

SELECT *FROM ( SELECT location, customer_id, sale_amount FROM cust_sales_category WHERE location <> 'south')PIVOT ( SUM(sale_amount) FOR customer_id IN (1, 2, 3, 4));

You’ll get the same results.

Aliasing PIVOT Columns

The queries we’ve looked at so far will display the column headers as the customer_id values that are stored in the table. What if you want to givethem a different name? The PIVOT keyword allows you to specify a column alias. This can be done on both the pivot_clause and the pivot_in_clause.

This example gives an alias to the SUM value:

SELECT *FROM ( SELECT location, customer_id, sale_amount FROM cust_sales_category)PIVOT ( SUM(sale_amount) AS sales_total FOR customer_id IN (1, 2, 3, 4));

You can see that we have aliased the SUM(sale_amount) using AS sales_total. The results of this query are:

The Oracle PIVOT column names are now displaying as 1_SALES_TOTAL, 2_SALES_TOTAL, and so on. This has been determined by the <customer_id>_<alias_name>. The first column of 1_SALES_TOTAL is the sales_total value for customer_id 1.

Instead, you could alias the column values inside the IN clause:

SELECT *FROM ( SELECT location, customer_id, sale_amount FROM cust_sales_category)PIVOT ( SUM(sale_amount) FOR customer_id IN (1 AS cust1, 2 AS cust2, 3 AS cust3, 4 AS cust4));

The columns values are showing exactly what you aliased them as. Instead of showing 1 it shows CUST1.

Finally, you can combine the pivot_clause alias and the pivot_in_clause alias:

SELECT *FROM ( SELECT location, customer_id, sale_amount FROM cust_sales_category)PIVOT ( SUM(sale_amount) AS sales_total FOR customer_id IN (1 AS cust1, 2 AS cust2, 3 AS cust3, 4 AS cust4));

This has concatenated both the alias from the pivot_in_clause (cust1) and the alias from the pivot_clause (sales_total), separating them by an underscore: cust1_sales_total.

Perform Multiple Aggregations

In the examples so far, we performed a single aggregation on a single column: a SUM on the different customer_id combinations. We can expand this in the SQL PIVOT query to do more if we want to.

We can add a second aggregate function to our PIVOT query. For example, let’s say along with showing the SUM, we want to show the COUNT of records in each group. The query would look like this:

SELECT *FROM ( SELECT location, customer_id, sale_amount FROM cust_sales_category)PIVOT ( SUM(sale_amount), COUNT(sale_amount) FOR customer_id IN (1, 2, 3, 4));

If we run this query, we get an error:

ORA-00918: column ambiguously defined00918. 00000 - "column ambiguously defined"*Cause:*Action:

To resolve this, we need to give the SUM and COUNT clauses an alias:

SELECT *FROM ( SELECT location, customer_id, sale_amount FROM cust_sales_category)PIVOT ( SUM(sale_amount) AS sum_sales, COUNT(sale_amount) AS count_sales FOR customer_id IN (1, 2, 3, 4));

The results of this query are:

You can see that for each value of customer_id, the SUM of the sales is shown, and then the COUNT. The column aliases have included both the customer_id and the alias of either sum_sales or count_sales. This allows us to use Oracle PIVOT on multiple columns.

Group By Multiple Columns

Another way to use multiple columns in your PIVOT query is to group by multiple columns. So far we’ve only been grouping by a customer_id. What if you wanted to group by customer_id and category?

You can do that

SELECT *FROM ( SELECT location, prod_category, customer_id, sale_amount FROM cust_sales_category)PIVOT ( SUM(sale_amount) FOR (customer_id, prod_category) IN ( (1, 'furniture') AS furn1, (2, 'furniture') AS furn2, (1, 'electronics') AS elec1, (2, 'electronics') AS elec2 ));

In this query, the FOR clause includes two columns: the customer_id and the prod_category. This prod_category was also added into the SELECT subquery at the start as it wasn’t included earlier.

Now we’ve specified the two columns in the FOR clause, we need to add both columns into the IN clause. Each of the IN criteria has now been included in brackets, which specifies a value for the customer_id and prod_category (e.g. 1, ‘furniture’). We’ve excluded many of the other records just for this example.

We’ve also given them a column alias to make them easier to read.

If you run this query, this is the result you’ll get:

The results show the SUM of sales for the furniture and electronics categories and for customer_id 1 and 2. The groups here can be customised in your IN clause to whatever you need.

PIVOT Example with XML

The PIVOT keyword allows you to show your results in an XML format. This is as simple as adding the XML keyword after the PIVOT keyword. Using our earlier example, we can display the output in an XML format.

SELECT *FROM ( SELECT location, customer_id, sale_amount FROM cust_sales_category)PIVOT XML ( SUM(sale_amount) AS sales_total FOR customer_id IN (1, 2, 3, 4));

If we just add the XML keyword and run this query, we get this error:

ORA-00905: missing keyword00905. 00000 - "missing keyword"*Cause:*Action:

This is because we can’t specify the values inside the IN clause. We’ll need to either use a subquery or use the keyword ANY.

XML with ANY

Here’s an example of a query with the XML parameter with the ANY keyword.

SELECT *FROM ( SELECT location, customer_id, sale_amount FROM cust_sales_category)PIVOT XML ( SUM(sale_amount) AS sales_total FOR customer_id IN (ANY));

Using the keyword ANY means that the grouping is performed on all values of customer_id. The result of this query is:

By default, SQL Developer will show the value of “XMLTYPE” for these XML columns. It will return the full value if you run it as a script (instead of a grid), or if it’s run as part of an application. If you want to see this in the grid, you can change a setting in SQL Developer:

1. Open the Tools menu and click Preferences.
2. Expand the Database section and click on Advanced
3. Check the box for “Display XML Value in Grid”
4. Click OK

Now you can rerun the query and the full value is displayed.

The data is translated into an XML format. The values above have been shortened to clean up the table, and a full value looks like this:

<PivotSet><item><column name = "CUSTOMER_ID">3</column><column name = "SALES_TOTAL">433</column></item><item><column name = "CUSTOMER_ID">4</column><column name = "SALES_TOTAL">584</column></item></PivotSet>

This can be useful if your application is processing XML. However, it makes it a little hard to read compared to a normal grid.

XML with Subquery

Another way to define the columns in a pivot query that uses an XML output is to use a subquery. The query we had earlier used the keyword ANY to define the columns in the IN clause.

SELECT *FROM ( SELECT location, customer_id, sale_amount FROM cust_sales_category)PIVOT XML ( SUM(sale_amount) AS sales_total FOR customer_id IN (ANY));

Rather than use the ANY keyword, you can use a subquery to define which column values you want to include.

SELECT *FROM ( SELECT location, customer_id, sale_amount FROM cust_sales_category)PIVOT XML ( SUM(sale_amount) AS sales_total FOR customer_id IN ( SELECT customer_id FROM cust_sales_category ));

The subquery in the IN clause is:

SELECT customer_id FROM cust_sales_category

This will find all of the customer_id values from this table. It has the same result as the ANY keyword.

Using a subquery means you have more control over what groups are used. You can add a WHERE clause to this subquery to only show certain customer_id values:

SELECT *FROM ( SELECT location, customer_id, sale_amount FROM cust_sales_category)PIVOT XML ( SUM(sale_amount) AS sales_total FOR customer_id IN ( SELECT customer_id FROM cust_sales_category WHERE customer_id <= 3 ));

This will create column groups for all customer_ids less than or equal to 3.

I haven’t used this XML output much at all, but if you need to use it in your query or application, then it’s quite useful.

Dynamically Specifying Columns

In a normal pivot query, or one that outputs the data as columns and not as XML, you had to specify which columns you wanted to include in the group:

SELECT *FROM ( SELECT location, customer_id, sale_amount FROM cust_sales_category)PIVOT ( SUM(sale_amount) FOR customer_id IN (1, 2, 3, 4));

If you want to group by all of these values, is there a way to say that, rather than list all of the values individually?

Unfortunately, there isn’t. You can’t use the ANY keyword or use a subquery in the IN clause unless you have the XML keyword applied.

This query will return an error:

SELECT *FROM ( SELECT location, customer_id, sale_amount FROM cust_sales_category)PIVOT ( SUM(sale_amount) FOR customer_id IN (ANY));

ORA-00936: missing expression00936. 00000 - "missing expression"*Cause:*Action:

Transposing Columns into Rows with UNPIVOT

The SQL PIVOT keyword we’ve looked at above will translate rows into columns. Oracle offers an UNPIVOT keyword, which performs the opposite. It transforms columns into rows.

The syntax of the SQL UNPIVOT keyword is:

SELECT columnsFROM tableUNPIVOT [INCLUDE|EXCLUDE NULLS] ( unpivot_clause, unpivot_for_clause, unpivot_in_clause)WHERE criteria;

It has a similar structure to the PIVOT keyword, with a few differences:

• It allows you to treat NULL values in a specific way.
• It does not include the XML keyword.
• It does not de-aggregate rows, as the query has no knowledge of the data behind the aggregation.

There are a few clauses with the UNPIVOT keyword:

• unpivot_clause: This specifies the name of the column for each of the column values from the pivoted data.
• unpivot_for_clause: This specifies the name of the column for the number values shown in the pivoted data.
• unpivot_in_clause: This specifies the list of pivoted columns.

If this sounds confusing, some examples will help to explain it better.

A Simple UNPIVOT Example

To demonstrate the SQL UNPIVOT keyword, we need to have some pivoted data first. We could supply a subquery, but then the UNPIVOT query would be very confusing. So, let’s create a view that shows pivoted data.

We can create a view called pivoted_sales.

CREATE VIEW pivoted_sales ASSELECT *FROM ( SELECT location, customer_id, sale_amount FROM cust_sales_category)PIVOT ( SUM(sale_amount) FOR customer_id IN ( 1 AS cust1, 2 AS cust2, 3 AS cust3, 4 AS cust4 ));

After the view is created, we can select from it to check the data:

SELECT *FROM pivoted_sales;

Now let’s look at an example of the UNPIVOT keyword.

SELECT *FROM pivoted_salesUNPIVOT ( total_sales FOR customer_id IN (cust1, cust2, cust3, cust4));

The results of this query are:

In this query:

• The unpivot_clause is “total_sales”, which in the results is the name of the last column. This column contains the number values or the SUM of the sales for each customer and region.
• The unpivot_for_clause is “customer_id”. This is the name given to the second column, which is populated from the different column headers in the pivoted data.
• The unpivot_in_clause is each of the column headers from the pivoted data. The columns from the underlying table are cust1, cust2, cust3, and cust4, and these are specified in the IN clause.

Handling NULL Values in UNPIVOT

In the output above, you’ll notice that there are 13 rows returned. However, there are 5 locations and 4 customers. Shouldn’t the query have returned 20 results, which is 5 x 4 and therefore all combinations of locations and customers? Why didn’t the query do that?

It’s because the data we selected from had no values for some combinations of location and customer. The location of “west” had returned rows for customer 1 and 3, so that would mean there are no rows for customer 2 and 4. Let’s query the table and check.

SELECT *FROM pivoted_sales;

You can see for the row with the location of “west”, there is a value for cust1 and cust3, but not cust2 or cust4.

The Oracle UNPIVOT keyword does not show results where the underlying data is NULL. There are 7 NULL values in this table, so of the 20 possible values, these 7 weren’t shown, resulting in only 13 values.

This behaviour is the default, but it can be changed. If you add the keyword INCLUDE NULLS after the UNPIVOT keyword, you’ll get records with NULL values in your results.

SELECT *FROM pivoted_salesUNPIVOT INCLUDE NULLS ( total_sales FOR customer_id IN (cust1, cust2, cust3, cust4));

The result shows 20 rows. The original 13 rows are included, as well as the 7 rows that have NULL value for the total_sales. The INCLUDE NULLS keyword that was used here has caused these values to be included.

The other keyword that can be used here is EXCLUDE NULLS. This is the same as the default behaviour, similar to many other parameters such as ORDER BY ASC being the default. If you want something to specifically exclude NULL values and not rely on the default, then it’s best to specify it:

SELECT *FROM pivoted_salesUNPIVOT EXCLUDE NULLS ( total_sales FOR customer_id IN (cust1, cust2, cust3, cust4));

So that’s how you can handle NULL values in an UNPIVOT query.

Aliases and UNPIVOT

Just like with the PIVOT keyword, we can apply aliases to our data with UNPIVOT. However, we can only do this with the pivot_in_clause. For example:

SELECT *FROM pivoted_salesUNPIVOT ( total_sales FOR customer_id IN ( cust1 AS 'cust 1 sales', cust2 AS 'cust 2 sales', cust3 AS 'cust 3 sales', cust4 AS 'cust 4 sales' ));

The result of this query is:

The values in the customer_id column have been translated to the values specified. They need to be included in single quotes. If they aren’t, you’ll get an error, just like this:

SELECT *FROM pivoted_salesUNPIVOT ( total_sales FOR customer_id IN ( cust1 AS cust_1_sales, cust2 AS cust_2_sales, cust3 AS cust_3_sales, cust4 AS cust_4_sales ));

ORA-56901: non-constant expression is not allowed for pivot|unpivot values56901. 00000 - "non-constant expression is not allowed for pivot|unpivot values"*Cause: Attempted to use non-constant expression for pivot|unpivot values.*Action: Use constants for pivot|unpivot values.Error at Line: 339 Column: 12

This ORA-56901 error (non-constant expression is not allowed for pivot|unpivot values) means you have to use a constant value for your aliases. You can enclose your aliases in single quotes to fix this error.

Can You Use Oracle PIVOT Without an Aggregate?

No, SQL PIVOT only works with aggregate functions. If you wanted to transpose rows to columns without losing the detail in your data, you could try a MAX or MIN function.

Let’s say you had a list of bugs in a bug_result table and different results for each stage:

If you wanted to use PIVOT on this data set, you can do it using the MAX function on the RESULT column.

SELECT *FROM bug_resultPIVOT ( MAX(result) FOR stage IN ('Dev', 'Test', 'UAT'));

The MAX and MIN functions work just as well with VARCHAR2 or text values as they do with dates and numbers. So if you run this query on your database, you’ll get a result like this:

So that’s how you can generate a pivot table in Oracle SQL without aggregates. It’s great for summarising results like this that don’t have a number value.

Conclusion

The Oracle PIVOT and UNPIVOT features are powerful and are a very useful way to transform your data. They are often used with numerical values to aggregate data but can be used with text and date values as well.

It includes an XML feature to export your data in an XML format. You can also add aliases to the PIVOT rows and columns, add a WHERE clause, and perform multiple aggregations and groups.

Take some time to practice using this handy SQL feature and you’ll be better able to work with your data.

What feature about PIVOT and UNPIVOT do you find most useful? Let me know in the comments below.

While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here: