Home » Software Development » Are You Using SQL PIVOT Yet? You Should!

About Lukas Eder

Lukas is a Java and SQL enthusiast developer. He created the Data Geekery GmbH. He is the creator of jOOQ, a comprehensive SQL library for Java, and he is blogging mostly about these three topics: Java, SQL and jOOQ.

Are You Using SQL PIVOT Yet? You Should!

Every once in a while, we run into these rare SQL issues where we’d like to do something that seems out of the ordinary. One of these things is pivoting rows to columns.

A recent question on Stack Overflow by Valiante asked for precisely this. Going from this table:
 
 
 
 
 
 

+------+------------+----------------+-------------------+
| dnId | propNameId |  propertyName  |   propertyValue   |
+------+------------+----------------+-------------------+
|    1 |         10 | objectsid      | S-1-5-32-548      |
|    1 |         19 | _objectclass   | group             |
|    1 |         80 | cn             | Account Operators |
|    1 |         82 | samaccountname | Account Operators |
|    1 |         85 | name           | Account Operators |
|    2 |         10 | objectsid      | S-1-5-32-544      |
|    2 |         19 | _objectclass   | group             |
|    2 |         80 | cn             | Administrators    |
|    2 |         82 | samaccountname | Administrators    |
|    2 |         85 | name           | Administrators    |
|    3 |         10 | objectsid      | S-1-5-32-551      |
|    3 |         19 | _objectclass   | group             |
|    3 |         80 | cn             | Backup Operators  |
|    3 |         82 | samaccountname | Backup Operators  |
|    3 |         85 | name           | Backup Operators  |
+------+------------+----------------+-------------------+

… we’d like to transform rows into colums as such:

+------+--------------+--------------+-------------------+-------------------+-------------------+
| dnId |  objectsid   | _objectclass |        cn         |  samaccountname   |       name        |
+------+--------------+--------------+-------------------+-------------------+-------------------+
|    1 | S-1-5-32-548 | group        | Account Operators | Account Operators | Account Operators |
|    2 | S-1-5-32-544 | group        | Administrators    | Administrators    | Administrators    |
|    3 | S-1-5-32-551 | group        | Backup Operators  | Backup Operators  | Backup Operators  |
+------+--------------+--------------+-------------------+-------------------+-------------------+

The idea is that we only want one row per distinct dnId, and then we’d like to transform the property-name-value pairs into columns, one column per property name.

Using Oracle or SQL Server PIVOT

The above transformation is actually quite easy with Oracle and SQL Server, which both support the PIVOT keyword on table expressions.

Here is how the desired result can be produced with SQL Server:

SELECT p.*
FROM (
  SELECT dnId, propertyName, propertyValue
  FROM myTable
) AS t
PIVOT(
  MAX(propertyValue)
  FOR propertyName IN (
    objectsid, 
    _objectclass, 
    cn, 
    samaccountname, 
    name
  )
) AS p;

(SQLFiddle here)

And the same query with a slightly different syntax in Oracle:

SELECT p.*
FROM (
  SELECT dnId, propertyName, propertyValue
  FROM myTable
) t
PIVOT(
  MAX(propertyValue)
  FOR propertyName IN (
    'objectsid'      as "objectsid", 
    '_objectclass'   as "_objectclass", 
    'cn'             as "cn", 
    'samaccountname' as "samaccountname", 
    'name'           as "name"
  )
) p;

(SQLFiddle here)

How does it work?

It is important to understand that PIVOT (much like JOIN) is a keyword that is applied to a table reference in order to transform it. In the above example, we’re essentially transforming the derived table t to form the pivot table p. We could take this further and join p to another derived table as so:

SELECT *
FROM (
  SELECT dnId, propertyName, propertyValue
  FROM myTable
) t
PIVOT(
  MAX(propertyValue)
  FOR propertyName IN (
    'objectsid'      as "objectsid", 
    '_objectclass'   as "_objectclass", 
    'cn'             as "cn", 
    'samaccountname' as "samaccountname", 
    'name'           as "name"
  )
) p
JOIN (
  SELECT dnId, COUNT(*) availableAttributes
  FROM myTable
  GROUP BY dnId
) q USING (dnId);

The above query will now allow for finding those rows for which there isn’t a name / value pair in every column. Let’s assume we remove one of the entries from the original table, the above query might now return:

| DNID |    OBJECTSID | _OBJECTCLASS |                CN |    SAMACCOUNTNAME |              NAME | AVAILABLEATTRIBUTES |
|------|--------------|--------------|-------------------|-------------------|-------------------|---------------------|
|    1 | S-1-5-32-548 |        group | Account Operators | Account Operators | Account Operators |                   5 |
|    2 | S-1-5-32-544 |        group |    Administrators |            (null) |    Administrators |                   4 |
|    3 | S-1-5-32-551 |        group |  Backup Operators |  Backup Operators |  Backup Operators |                   5 |

jOOQ also supports the SQL PIVOT clause through its API.

What if I don’t have PIVOT?

In simple PIVOT scenarios, users of other databases than Oracle or SQL Server can write an equivalent query that uses GROUP BY and MAX(CASE ...) expressions as documented in this answer here.

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

and many more ....

 

Leave a Reply

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

*


six + 3 =

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Want to take your Java Skills to the next level?
Grab our programming books for FREE!
  • Save time by leveraging our field-tested solutions to common problems.
  • The books cover a wide range of topics, from JPA and JUnit, to JMeter and Android.
  • Each book comes as a standalone guide (with source code provided), so that you use it as reference.
Last Step ...

Where should we send the free eBooks?

Good Work!
To download the books, please verify your email address by following the instructions found on the email we just sent you.