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:

locationcustomer_idsale_amount
north6875
south2378
east5136
west5236
central3174
north1729
east2147
west3200
north6987
central4584
south3714
east1192
west3946
east4649
south2503
north5399
central6259
east3407
west1545

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:

LOCATIONCUSTOMER_IDSUM(SALE_AMOUNT)
central3174
central4584
central6259
east1192
east2147
east3407
east4649
east5136
north1729
north5399
north61862
south2881
south3714
west1545
west31146
west5236

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:

location123456
central001745840259
east1921474076491360
north7290003991862
south0881714000

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;
LOCATIONCUSTOMER_IDSUM(SALE_AMOUNT)
central3174
central4584
central6259
east1192
east2147
east3407
east4649
east5136
north1729
north5399
north61862
south2881
south3714
west1545
west31146
west5236

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:

LOCATION123456
west545(null)1146(null)236(null)
central(null)(null)174584(null)259
north729(null)(null)(null)3991862
south(null)881714(null)(null)(null)
east192147407649136(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?

locationprod_categorycustomer_idsale_amount
northfurniture2875
southelectronics2378
eastgardening4136
westelectronics3236
centralfurniture3174
northelectronics1729
eastgardening2147
westelectronics3200
northfurniture4987
centralgardening4584
southelectronics3714
eastfurniture1192
westgardening3946
eastelectronics4649
southfurniture2503
northelectronics1399
centralgardening3259
eastelectronics3407
westfurniture1545

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:

locationprod_category1234
westgardening(null)(null)946(null)
westfurniture545(null)(null)(null)
eastelectronics(null)(null)407649
centralfurniture(null)(null)174(null)
northfurniture(null)875(null)987
eastfurniture192(null)(null)(null)
southfurniture(null)503(null)(null)
eastgardening(null)147(null)136
northelectronics1128(null)(null)(null)
westelectronics(null)(null)436(null)
centralgardening(null)(null)259584
southelectronics(null)378714(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:

location1234
west545(null)1382(null)
central(null)(null)433584
north1128875(null)987
south(null)881714(null)
east192147407785

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)
);
location1234
furniture7371378174987
electronics11283781557649
gardening(null)1471205720

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:

location1234
west545(null)1382(null)
central(null)(null)433584
north1128875(null)987
east192147407785

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:

location1_SALES_TOTAL2_SALES_TOTAL3_SALES_TOTAL4_SALES_TOTAL
west545(null)1382(null)
central(null)(null)433584
north1128875(null)987
south(null)881714(null)
east192147407785

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)
);
locationCUST1CUST2CUST3CUST4
west545(null)1382(null)
central(null)(null)433584
north1128875(null)987
south(null)881714(null)
east192147407785

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)
);
locationCUST1_SALES_TOTALCUST2_SALES_TOTALCUST3_SALES_TOTALCUST4_SALES_TOTAL
west545(null)1382(null)
central(null)(null)433584
north1128875(null)987
south(null)881714(null)
east192147407785

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:

location1_SUM_SALES1_COUNT_SALES2_SUM_SALES2_COUNT_SALES3_SUM_SALES3_COUNT_SALES4_SUM_SALES4_COUNT_SALES
west5451(null)013823(null)0
central(null)0(null)043325841
north112828751(null)09871
south(null)088127141(null)0
east1921147140717852

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:

locationFURN1FURN2ELEC1ELEC2
west545(null)(null)(null)
central(null)(null)(null)(null)
north(null)8751128(null)
south(null)503(null)378
east192(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:

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

LOCATIONCUSTOMER_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;
LOCATIONCUST1CUST2CUST3CUST4
west545(null)1382(null)
central(null)(null)433584
north1128875(null)987
south(null)881714(null)
east192147407785

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:

LOCATIONCUSTOMER_IDTOTAL_SALES
westCUST1545
westCUST31382
centralCUST3433
centralCUST4584
northCUST11128
northCUST2875
northCUST4987
southCUST2881
southCUST3714
eastCUST1192
eastCUST2147
eastCUST3407
eastCUST4785

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;
LOCATIONCUST1CUST2CUST3CUST4
west545(null)1382(null)
central(null)(null)433584
north1128875(null)987
south(null)881714(null)
east192147407785

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)
);
LOCATIONCUSTOMER_IDTOTAL_SALES
westCUST1545
westCUST2(null)
westCUST31382
westCUST4(null)
centralCUST1(null)
centralCUST2(null)
centralCUST3433
centralCUST4584
northCUST11128
northCUST2875
northCUST3(null)
northCUST4987
southCUST1(null)
southCUST2881
southCUST3714
southCUST4(null)
eastCUST1192
eastCUST2147
eastCUST3407
eastCUST4785

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:

LOCATIONCUSTOMER_IDTOTAL_SALES
westcust 1 sales545
westcust 3 sales1382
centralcust 3 sales433
centralcust 4 sales584
northcust 1 sales1128
northcust 2 sales875
northcust 4 sales987
southcust 2 sales881
southcust 3 sales714
eastcust 1 sales192
eastcust 2 sales147
eastcust 3 sales407
eastcust 4 sales785

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_IDSTAGERESULT
1DevPass
1TestPass
1UATFail
2DevPass
2TestFail
3DevPass
3TestFail
3UATPass

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_IDDevTestUAT
1PassPassFail
2PassFail(null)
3PassFailPass

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: