FB

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. You can click on any of these entries to be taken to that part on this page:Oracle SQL Pivot

Now let’s get into the guide!

 

The Problem

Back to Top

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.

Get All SQL Scripts Used In This Guide

 

The PIVOT Keyword in Oracle SQL

Back to Top

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

Back to Top

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

Back to Top

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

Back to Top

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 PIVOT clause must come after the WHERE 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

Back to Top

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

Back to Top

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

Back to Top

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

Back to Top

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

Back to Top

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

Back to Top

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

Back to Top

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

Back to Top

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

Back to Top

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?

Back to Top

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

Back to Top

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.

Get All SQL Scripts Used In This Guide

Download the SQL Files From This Page: