Oracle SQL PIVOT and UNPIVOT: The Complete Guide

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).

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:

1SELECT location, customer_id, SUM(sale_amount)
2FROM cust_sales
3GROUP BY location, customer_id
4ORDER 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:

| location | 1 | 2 | 3 | 4 | 5 | 6 | | central | 0 | 0 | 174 | 584 | 0 | 259 | | east | 192 | 147 | 407 | 649 | 136 | 0 | | north | 729 | 0 | 0 | 0 | 399 | 1862 | | south | 0 | 881 | 714 | 0 | 0 | 0 |

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.

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:

1SELECT columns
2FROM tables
3PIVOT [XML] (
4  pivot_clause,
5  pivot_for_clause,
6  pivot_in_clause
7);

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:

1SELECT location,
2customer_id,
3SUM(sale_amount)
4FROM cust_sales
5GROUP BY location, customer_id
6ORDER BY location, customer_id;

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

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

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..

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

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.

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

The result of this query is:

1ORA-00904: "SALE_AMOUNT": invalid identifier
200904. 00000 -  "%s: invalid identifier"
3*Cause:
4*Action:
5Error 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:

 1SELECT *
 2FROM (
 3  SELECT location, customer_id, sale_amount
 4  FROM cust_sales_category
 5)
 6PIVOT (
 7  SUM(sale_amount)
 8  FOR customer_id
 9  IN (1, 2, 3, 4)
10);

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:

| location | 1 | 2 | 3 | 4 | | --- | --- | --- | --- | | west | 545 | (null) | 1382 | (null) | | central | (null) | (null) | 433 | 584 | | north | 1128 | 875 | (null) | 987 | | south | (null) | 881 | 714 | (null) | | east | 192 | 147 | 407 | 785 |

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:

 1SELECT *
 2FROM (
 3  SELECT prod_category, customer_id, sale_amount
 4  FROM cust_sales_category
 5)
 6PIVOT (
 7  SUM(sale_amount)
 8  FOR customer_id
 9  IN (1, 2, 3, 4)
10);

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:

 1SELECT *
 2FROM (
 3  SELECT location, customer_id, sale_amount
 4  FROM cust_sales_category
 5)
 6WHERE location <> 'south'
 7PIVOT (
 8  SUM(sale_amount)
 9  FOR customer_id
10  IN (1, 2, 3, 4)
11);

1ORA-00933: SQL command not properly ended
200933.00000 -  "SQL command not properly ended"
3*Cause:
4*Action:
5Error at Line: 127 Column: 1

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

 1SELECT *
 2FROM (
 3  SELECT location, customer_id, sale_amount
 4  FROM cust_sales_category
 5)
 6PIVOT (
 7  SUM(sale_amount)
 8  FOR customer_id
 9  IN (1, 2, 3, 4)
10)
11WHERE 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:

 1SELECT *
 2FROM (
 3  SELECT location, customer_id, sale_amount
 4  FROM cust_sales_category
 5  WHERE location <> 'south'
 6)
 7PIVOT (
 8  SUM(sale_amount)
 9  FOR customer_id
10  IN (1, 2, 3, 4)
11);

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 give them 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:

 1SELECT *
 2FROM (
 3  SELECT location, customer_id, sale_amount
 4  FROM cust_sales_category
 5)
 6PIVOT (
 7  SUM(sale_amount) AS sales_total
 8  FOR customer_id
 9  IN (1, 2, 3, 4)
10);

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:

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

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:

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

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:

 1SELECT *
 2FROM (
 3  SELECT location, customer_id, sale_amount
 4  FROM cust_sales_category
 5)
 6PIVOT (
 7  SUM(sale_amount),
 8  COUNT(sale_amount)
 9  FOR customer_id
10  IN (1, 2, 3, 4)
11);

If we run this query, we get an error:

1ORA-00918: column ambiguously defined
200918. 00000 -  "column ambiguously defined"
3*Cause:
4*Action:

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

 1SELECT *
 2FROM (
 3  SELECT location, customer_id, sale_amount
 4  FROM cust_sales_category
 5)
 6PIVOT (
 7  SUM(sale_amount) AS sum_sales,
 8  COUNT(sale_amount) AS count_sales
 9  FOR customer_id
10  IN (1, 2, 3, 4)
11);

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

 1SELECT *
 2FROM (
 3  SELECT location, prod_category, customer_id, sale_amount
 4  FROM cust_sales_category
 5)
 6PIVOT (
 7  SUM(sale_amount)
 8  FOR (customer_id, prod_category)
 9  IN (
10    (1, 'furniture') AS furn1,
11    (2, 'furniture') AS furn2,
12    (1, 'electronics') AS elec1,
13    (2, 'electronics') AS elec2
14  )
15);

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.

 1SELECT *
 2FROM (
 3  SELECT location, customer_id, sale_amount
 4  FROM cust_sales_category
 5)
 6PIVOT XML (
 7  SUM(sale_amount) AS sales_total
 8  FOR customer_id
 9  IN (1, 2, 3, 4)
10);

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

1ORA-00905: missing keyword
200905. 00000 -  "missing keyword"
3*Cause:
4*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.

 1SELECT *
 2FROM (
 3  SELECT location, customer_id, sale_amount
 4  FROM cust_sales_category
 5)
 6PIVOT XML (
 7  SUM(sale_amount) AS sales_total
 8  FOR customer_id
 9  IN (ANY)
10);

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:

1<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.

 1SELECT *
 2FROM (
 3  SELECT location, customer_id, sale_amount
 4  FROM cust_sales_category
 5)
 6PIVOT XML (
 7  SUM(sale_amount) AS sales_total
 8  FOR customer_id
 9  IN (ANY)
10);

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

 1SELECT *
 2FROM (
 3  SELECT location, customer_id, sale_amount
 4  FROM cust_sales_category
 5)
 6PIVOT XML (
 7  SUM(sale_amount) AS sales_total
 8  FOR customer_id
 9  IN (
10    SELECT customer_id
11    FROM cust_sales_category
12  )
13);

The subquery in the IN clause is:

1SELECT 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:

 1SELECT *
 2FROM (
 3  SELECT location, customer_id, sale_amount
 4  FROM cust_sales_category
 5)
 6PIVOT XML (
 7  SUM(sale_amount) AS sales_total
 8  FOR customer_id
 9  IN (
10    SELECT customer_id
11    FROM cust_sales_category
12    WHERE customer_id <= 3
13  )
14);

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:

 1SELECT *
 2FROM (
 3  SELECT location, customer_id, sale_amount
 4  FROM cust_sales_category
 5)
 6PIVOT (
 7  SUM(sale_amount)
 8  FOR customer_id
 9  IN (1, 2, 3, 4)
10);

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:

 1SELECT *
 2FROM (
 3  SELECT location, customer_id, sale_amount
 4  FROM cust_sales_category
 5)
 6PIVOT (
 7  SUM(sale_amount)
 8  FOR customer_id
 9  IN (ANY)
10);

1ORA-00936: missing expression
200936. 00000 -  "missing expression"
3*Cause:
4*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:

1SELECT columns
2FROM table
3UNPIVOT [INCLUDE|EXCLUDE NULLS] (
4  unpivot_clause,
5  unpivot_for_clause,
6  unpivot_in_clause
7)
8WHERE 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.

 1CREATE VIEW pivoted_sales AS
 2SELECT *
 3FROM (
 4  SELECT location, customer_id, sale_amount
 5  FROM cust_sales_category
 6)
 7PIVOT (
 8  SUM(sale_amount)
 9  FOR customer_id
10  IN (
11    1 AS cust1,
12    2 AS cust2,
13    3 AS cust3,
14    4 AS cust4
15  )
16);

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

1SELECT *
2FROM pivoted_sales;

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

1SELECT *
2FROM pivoted_sales
3UNPIVOT (
4  total_sales
5  FOR customer_id
6  IN (cust1, cust2, cust3, cust4)
7);

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.

1SELECT *
2FROM 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.

1SELECT *
2FROM pivoted_sales
3UNPIVOT INCLUDE NULLS (
4  total_sales
5  FOR customer_id
6  IN (cust1, cust2, cust3, cust4)
7);

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:

1SELECT *
2FROM pivoted_sales
3UNPIVOT EXCLUDE NULLS (
4  total_sales
5  FOR customer_id
6  IN (cust1, cust2, cust3, cust4)
7);

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:

 1SELECT *
 2FROM pivoted_sales
 3UNPIVOT (
 4  total_sales
 5  FOR customer_id
 6  IN (
 7    cust1 AS 'cust 1 sales',
 8    cust2 AS 'cust 2 sales',
 9    cust3 AS 'cust 3 sales',
10    cust4 AS 'cust 4 sales'
11  )
12);

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:

 1SELECT *
 2FROM pivoted_sales
 3UNPIVOT (
 4  total_sales
 5  FOR customer_id
 6  IN (
 7    cust1 AS cust_1_sales,
 8    cust2 AS cust_2_sales,
 9    cust3 AS cust_3_sales,
10    cust4 AS cust_4_sales
11  )
12);

1ORA-56901: non-constant expression is not allowed for pivot|unpivot values
256901. 00000 -  "non-constant expression is not allowed for pivot|unpivot values"
3*Cause:    Attempted to use non-constant expression for pivot|unpivot values.
4*Action:   Use constants for pivot|unpivot values.
5
6Error 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.

1SELECT *
2FROM bug_result
3PIVOT (
4  MAX(result)
5  FOR stage
6  IN ('Dev', 'Test', 'UAT')
7);

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.