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

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:

location	customer_id	sale_amount
north	6	875
south	2	378
east	5	136
west	5	236
central	3	174
north	1	729
east	2	147
west	3	200
north	6	987
central	4	584
south	3	714
east	1	192
west	3	946
east	4	649
south	2	503
north	5	399
central	6	259
east	3	407
west	1	545

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_sales
GROUP BY location, customer_id
ORDER BY location, customer_id;

This will show the following results:

LOCATION	CUSTOMER_ID	SUM(SALE_AMOUNT)
central	3	174
central	4	584
central	6	259
east	1	192
east	2	147
east	3	407
east	4	649
east	5	136
north	1	729
north	5	399
north	6	1862
south	2	881
south	3	714
west	1	545
west	3	1146
west	5	236

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.

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 columns
FROM tables
PIVOT [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_sales
GROUP BY location, customer_id
ORDER BY location, customer_id;

LOCATION	CUSTOMER_ID	SUM(SALE_AMOUNT)
central	3	174
central	4	584
central	6	259
east	1	192
east	2	147
east	3	407
east	4	649
east	5	136
north	1	729
north	5	399
north	6	1862
south	2	881
south	3	714
west	1	545
west	3	1146
west	5	236

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

SELECT *
FROM cust_sales
PIVOT (
  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:

LOCATION	1	2	3	4	5	6
west	545	(null)	1146	(null)	236	(null)
central	(null)	(null)	174	584	(null)	259
north	729	(null)	(null)	(null)	399	1862
south	(null)	881	714	(null)	(null)	(null)
east	192	147	407	649	136	(null)

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?

location	prod_category	customer_id	sale_amount
north	furniture	2	875
south	electronics	2	378
east	gardening	4	136
west	electronics	3	236
central	furniture	3	174
north	electronics	1	729
east	gardening	2	147
west	electronics	3	200
north	furniture	4	987
central	gardening	4	584
south	electronics	3	714
east	furniture	1	192
west	gardening	3	946
east	electronics	4	649
south	furniture	2	503
north	electronics	1	399
central	gardening	3	259
east	electronics	3	407
west	furniture	1	545

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_category
PIVOT (
  SUM(sale_amount)
  FOR customer_id
  IN (1, 2, 3, 4)
);

The results are:

location	prod_category	1	2	3	4
west	gardening	(null)	(null)	946	(null)
west	furniture	545	(null)	(null)	(null)
east	electronics	(null)	(null)	407	649
central	furniture	(null)	(null)	174	(null)
north	furniture	(null)	875	(null)	987
east	furniture	192	(null)	(null)	(null)
south	furniture	(null)	503	(null)	(null)
east	gardening	(null)	147	(null)	136
north	electronics	1128	(null)	(null)	(null)
west	electronics	(null)	(null)	436	(null)
central	gardening	(null)	(null)	259	584
south	electronics	(null)	378	714	(null)

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_amount
FROM cust_sales_category
PIVOT (
  SUM(sale_amount)
  FOR customer_id
  IN (1, 2, 3, 4)
);

The result of this query is:

ORA-00904: "SALE_AMOUNT": invalid identifier
00904. 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:

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:

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

location	1	2	3	4
furniture	737	1378	174	987
electronics	1128	378	1557	649
gardening	(null)	147	1205	720

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 ended
00933.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:

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

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

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:

location	1_SALES_TOTAL	2_SALES_TOTAL	3_SALES_TOTAL	4_SALES_TOTAL
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 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)
);

location	CUST1	CUST2	CUST3	CUST4
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 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)
);

location	CUST1_SALES_TOTAL	CUST2_SALES_TOTAL	CUST3_SALES_TOTAL	CUST4_SALES_TOTAL
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

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 defined
00918. 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:

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

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:

location	FURN1	FURN2	ELEC1	ELEC2
west	545	(null)	(null)	(null)
central	(null)	(null)	(null)	(null)
north	(null)	875	1128	(null)
south	(null)	503	(null)	378
east	192	(null)	(null)	(null)

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 keyword
00905. 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:

LOCATION	CUSTOMER_ID_XML
central	(XMLTYPE)
east	(XMLTYPE)
north	(XMLTYPE)
south	(XMLTYPE)
west	(XMLTYPE)

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.

LOCATION	CUSTOMER_ID_XML
central	<PivotSet><item><column name = “CUSTOMER_ID”>3</column>…
east	<PivotSet><item><column name = “CUSTOMER_ID”>1</column>…
north	<PivotSet><item><column name = “CUSTOMER_ID”>1</column>
south	<PivotSet><item><column name = “CUSTOMER_ID”>2</column>
west	<PivotSet><item><column name = “CUSTOMER_ID”>1</column>

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 expression
00936. 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 columns
FROM table
UNPIVOT [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 AS
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
  )
);

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

SELECT *
FROM pivoted_sales;

LOCATION	CUST1	CUST2	CUST3	CUST4
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

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

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

The results of this query are:

LOCATION	CUSTOMER_ID	TOTAL_SALES
west	CUST1	545
west	CUST3	1382
central	CUST3	433
central	CUST4	584
north	CUST1	1128
north	CUST2	875
north	CUST4	987
south	CUST2	881
south	CUST3	714
east	CUST1	192
east	CUST2	147
east	CUST3	407
east	CUST4	785

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;

LOCATION	CUST1	CUST2	CUST3	CUST4
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

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_sales
UNPIVOT INCLUDE NULLS (
  total_sales
  FOR customer_id
  IN (cust1, cust2, cust3, cust4)
);

LOCATION	CUSTOMER_ID	TOTAL_SALES
west	CUST1	545
west	CUST2	(null)
west	CUST3	1382
west	CUST4	(null)
central	CUST1	(null)
central	CUST2	(null)
central	CUST3	433
central	CUST4	584
north	CUST1	1128
north	CUST2	875
north	CUST3	(null)
north	CUST4	987
south	CUST1	(null)
south	CUST2	881
south	CUST3	714
south	CUST4	(null)
east	CUST1	192
east	CUST2	147
east	CUST3	407
east	CUST4	785

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_sales
UNPIVOT 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_sales
UNPIVOT (
  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:

LOCATION	CUSTOMER_ID	TOTAL_SALES
west	cust 1 sales	545
west	cust 3 sales	1382
central	cust 3 sales	433
central	cust 4 sales	584
north	cust 1 sales	1128
north	cust 2 sales	875
north	cust 4 sales	987
south	cust 2 sales	881
south	cust 3 sales	714
east	cust 1 sales	192
east	cust 2 sales	147
east	cust 3 sales	407
east	cust 4 sales	785

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_sales
UNPIVOT (
  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 values
56901. 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:

BUG_ID	STAGE	RESULT
1	Dev	Pass
1	Test	Pass
1	UAT	Fail
2	Dev	Pass
2	Test	Fail
3	Dev	Pass
3	Test	Fail
3	UAT	Pass

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_result
PIVOT (
  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:

BUG_ID	Dev	Test	UAT
1	Pass	Pass	Fail
2	Pass	Fail	(null)
3	Pass	Fail	Pass

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: