Pivot Queries in Oracle and SQL Server

Pivot tables show summary information in a compact grid format. They can be done with standard SQL, but using the PIVOT clause makes them a lot easier to write, and – more importantly – a lot easier to read.

Here’s the table we’ll use for these pivot examples:

Column Name  Column Type
------------ -------------
ProductType  VARCHAR(20)
ProductColor VARCHAR(10)
Region       VARCHAR(20)
Units        DECIMAL(7)
TotalSales   DECIMAL(10,2)

 
The data types were chosen because they’re supported by both Oracle and SQL Server. The script to create and populate the table is here.

A fairly basic query will give you the total sales by product type and region:

SELECT ProductType, Region, SUM(TotalSales) AS TotSales
FROM TestPiv
GROUP BY ProductType, Region
ORDER BY ProductType, Region
ProductType Region TotSales
----------- ------ --------
Book        East   853.97
Book        North  886.44
Book        South  749.14
Book        West   798.65
Glove       East   152.72
Glove       North  285.46
Glove       South  316.21
Glove       West   219.47
Widget      East   402.23
Widget      North  503.19
Widget      South  273.88
Widget      West   499.65

 
These results are tablular, showing detail by row. Another common type of report is cross-tabular, with some results showing across columns. Here’s what it looks like; we’ll get into the actual query soon:

ProductType North  South  East   West
----------- ------ ------ ------ ------
Book        886.44 749.14 853.97 798.65
Glove       285.46 316.21 152.72 219.47
Widget      503.19 273.88 402.23 499.65

 
Think of the North, South, East and West values as being "pivoted" from rows to columns.

The PIVOT Clause

Oracle and SQL Server support the PIVOT clause to return cross-tabluated results, but the syntax is a bit unusual and less flexible than other query clauses.

The PIVOT clause defines a numeric value to cross-tabulate and the values to cross-tabulate for. An example is best here; I’ll give separate examples because there are slight syntax differences between Oracle and SQL Server.

The Oracle PIVOT Clause

... beginning of query, followed by:
PIVOT (
  SUM(TotalSales)
  FOR Region IN ('North', 'South', 'East', 'West')
)

 
The SQL Server PIVOT Clause

... beginning of query, followed by:
PIVOT (
  SUM(TotalSales)
  FOR Region IN (North, South, East, West)
) AS MyPivot

 
In conjunction with the rest of the query, this says that the sum of the total sales will be cross-tabulated for Region values of North, South, East, and West.

Here’s a quick summary of the syntax differences between Oracle and SQL Server:

  • In Oracle you need to put single quotes around the pivot values; in SQL Server you don’t.
  • The SQL Server pivot values must be valid SQL Server identifier names. If they’re not, surround them with square brackets: FOR Region IN ([North], [South], [East], [West]).
  • In SQL Server, you need to alias the PIVOT clause (... AS MyPivot). In Oracle it’s optional. Also note that the AS keyword is prohibited when aliasing a table in Oracle; in SQL Server it’s optional.

A Very Basic PIVOT Query

Let’s start with a basic pivot query. As you’ll see, this query will have a problem.

Oracle

SELECT * FROM TestPiv
PIVOT (
  SUM(TotalSales)
  FOR Region IN ('North', 'South', 'East', 'West')
)

 
SQL Server

SELECT * FROM TestPiv
PIVOT (
  SUM(TotalSales)
  FOR Region IN (North, South, East, West)
) AS MyPivot;

 
The Oracle results are shown below (the SQL Server results have the same data but they tend to sort differently, and the column headers will vary slightly):

PRODUCTTYPE UNITS 'North' 'South'  'East'  'West'
----------- ----- ------- ------- ------- -------
Glove          96          316.21
Widget         41                          499.65
Book          113                          798.65
Glove          87  285.46
Widget         42  503.19
Book          107          749.14
Widget         26          273.88
Widget         33                  402.23
Book          122  886.44
Book          119                  853.97
Glove          48                  152.72
Glove          72                          219.47

 
This query doesn’t give cross-tabulated results because the Units value is in the way. We want to cross-tabulate for ProductType by TotalSales per region. The Units don’t figure into it at all.

Limiting PIVOT Queries to Desired Columns (the wrong way)

Here’s why I think the pivot query syntax is a bit unusual: after seeing the results above, the natural inclination of most would be to try listing only the columns you want in the SELECT field clause…

-- Oracle syntax
SELECT ProductType, Region, TotalSales
FROM TestPiv
PIVOT (
  SUM(TotalSales)
  FOR Region IN ('North', 'South', 'East', 'West')
);

 
… or perhaps to list only the Product Type:

-- SQL Server syntax
SELECT ProductType
FROM TestPiv
PIVOT (
  SUM(TotalSales)
  FOR Region IN (North, South, East, West)
) AS MyPivot;

 
In fact, neither of these will work. The next section shows how to get the Units out of the query.

Limiting PIVOT Queries to Desired Columns (the right way)

The important thing to remember is that a pivot query works from a SELECT * column list, period. There’s no way around it. Since that’s the case, you have three options for getting the perfect query:

  1. Query a table or view that has the columns you need, and no more.
  2. Use a subquery to limit the columns.
  3. Use a Common Table Expression (CTE) to limit the columns.

The first option is obvious enough so I won’t cover it here.

Here’s how to do the subquery option:

Oracle

SELECT * FROM (
  SELECT ProductType, Region, TotalSales
  FROM TestPiv
)
PIVOT (
  SUM(TotalSales)
  FOR Region IN ('North', 'South', 'East', 'West')
);

 
SQL Server

SELECT * FROM (
  SELECT ProductType, Region, TotalSales
  FROM TestPiv
) AS PivSubset
PIVOT (
  SUM(TotalSales)
  FOR Region IN (North, South, East, West)
) AS MyPivot;

 
Note that SQL Server requires aliasing for the subquery as well as the pivot result.

And here’s how to do the CTE option:
 
Oracle

-- Oracle
WITH PivSubset AS (
  SELECT ProductType, Region, TotalSales
  FROM TestPiv
)
SELECT * FROM PivSubset
PIVOT (
  SUM(TotalSales)
  FOR Region IN ('North', 'South', 'East', 'West')
);

 
SQL Server

WITH PivSubset AS (
  SELECT ProductType, Region, TotalSales
  FROM TestPiv
)
SELECT * FROM PivSubset
PIVOT (
  SUM(TotalSales)
  FOR Region IN (North, South, East, West)
) PivResult;

Ordering PIVOT Results

SQL Server pivot queries normally order automatically by the main column, in this case ProductType, but there’s no guarantee they will. Oracle pivot queries don’t default to any particular order. The takeaway: always use ORDER BY for all production pivot queries. As with any other ORDER BY, just tack it on to the end of the query:
Oracle

SELECT * FROM (
  SELECT ProductType, Region, TotalSales
  FROM TestPiv
)
PIVOT (
  SUM(TotalSales)
  FOR Region IN ('North', 'South', 'East', 'West')
)
ORDER BY ProductType;

 
SQL Server

SELECT * FROM (
  SELECT ProductType, Region, TotalSales
  FROM TestPiv
) AS PivSubset
PIVOT (
  SUM(TotalSales)
  FOR Region IN (North, South, East, West)
) AS MyPivot
ORDER BY ProductType;

Assigning PIVOT Column Names

The pivot column names default to the pivot values – in our examples that’s North, South, East and West. Oracle adds single quotes around the column names, something to keep in mind if you’re accessing a pivot result using a data driver such as ODBC, ADO.NET, JDBC, OCI, etc.

In this section, we’ll change the result column names as follows (note that this isn’t the most useful transformation; its only purpose is to show how to specify the column name):

  • North: N
  • South: S
  • East: E
  • West: W

To change the output column name in Oracle, just give it an alias.

SQL Server doesn’t let you alias the pivot column names, so you’ll need to wrap the pivot query with an outer query that assigns aliases to the system-generated column names. Note that this can only be done with a "subquery" pivot query type – the CTE type won’t work because the outer query pushes the CTE table two levels down, and a CTE table must be on the top level.

Oracle

WITH PivSubset AS (
  SELECT ProductType, Region, TotalSales
  FROM TestPiv
)
SELECT * FROM PivSubset
PIVOT (
  SUM(TotalSales)
  FOR Region IN (
    'North' AS N,
    'South' AS S,
    'East'  AS E,
    'West'  AS W)
)
ORDER BY ProductType;

 
SQL Server

SELECT
  North AS N,
  South AS S,
  East  AS E,
  West  AS W
FROM (
  SELECT *
  FROM (
    SELECT ProductType, Region, TotalSales
    FROM TestPiv
  ) PivSubset
  PIVOT (
    SUM(TotalSales)
    FOR Region IN (North, South, East, West)
  ) PivResult
) PivOther
ORDER BY ProductType;

 
The query results are:

PRODUCTTYPE      N      S      E      W
----------- ------ ------ ------ ------
Book        886.44 749.14 853.97 798.65
Glove       285.46 316.21 152.72 219.47
Widget      503.19 273.88 402.23 499.65

Limitations

Pivot queries must know the pivot values ahead of time, which limits their usefulness when it comes to dynamic column values. You can easily pivot by the month of a date, or enumerated columns like gender or status (assuming you have just a few pre-defined status codes), but if you have an attribute that changes regularly you won’t know the values ahead of time. In this scenario you could build and execute the query dynamically, but I normally find it easier to get the tabular results and cross-tabulate it in the display or the data layer.

Leave a Reply

Your email address will not be published. Required fields are marked *