UNPIVOT in Oracle 11G to Select Columns As Rows

January 11, 2017

in SQL

UNPIVOT: Transpose Columns to Rows

When the need is to transpose columns as rows, Oracle 11G+ enables you to easily implement this using the UNPIVOT clause is SQL.

A typical scenario:

Customer data is validated against a set of audit checks and the validation results are stored against the Customer Id as Y/N flags. The checks in this example are:

Is name populated? Is name valid?
Is DOB populated? Is DOB valid?

The table:

SQL> desc validation_output
 Name                    Null?    Type
 ----------------------- -------- -----------
 CUSTID                           NUMBER(4)
 NAME_POPULATED                   VARCHAR2(1)
 NAME_VALID                       VARCHAR2(1)
 DOB_POPULATED                    VARCHAR2(1)
 DOB_VALID                        VARCHAR2(1)
 

The data:

SQL> select *
  2  from validation_output;

    CUSTID NAME_POPULATED NAME_VALID DOB_POPULATED DOB_VALID
---------- -------------- ---------- ------------- ---------
       100 Y              N          Y             Y
       101 N              N          N             N

The requirement is to transpose the columns NAME_POPULATED, NAME_VALID etc as rows – that is, display the result in this form:

    CUSTID CHECK_NAME       CHECK_RESULT
---------- ---------------- ------------
       100 NAME_POPULATED   Y
       100 NAME_VALID       N
       100 DOB_POPULATED    Y
       100 DOB_VALID        Y
       101 NAME_POPULATED   N
       101 NAME_VALID       N
       101 DOB_POPULATED    N
       101 DOB_VALID        N

8 rows selected.

Pre 11G: Using DECODE

Before 11G, the popular solution to the unpivot problem was using a combination of DECODE and the LEVEL pseudocolumn in a hierarchical query on DUAL table.

The SQL:

select custid,
  decode(unpivot_row, 1, 'NAME_POPULATED',
                      2, 'NAME_VALID',
                      3, 'DOB_POPULATED',
                      4, 'DOB_VALID',
                   'N/A') AS check_name,
  decode(unpivot_row, 1, NAME_POPULATED,
                      2, NAME_VALID,
                      3, DOB_POPULATED,
                      4, DOB_VALID,
                   'N/A') AS check_result
FROM validation_output,
    (select level as unpivot_row 
     from dual connect by level <= 4);

When run:

SQL> select custid,
  2    decode(unpivot_row, 1, 'NAME_POPULATED',
  3                        2, 'NAME_VALID',
  4                        3, 'DOB_POPULATED',
  5                        4, 'DOB_VALID',
  6                     'N/A') AS check_name,
  7    decode(unpivot_row, 1, NAME_POPULATED,
  8                        2, NAME_VALID,
  9                        3, DOB_POPULATED,
 10                        4, DOB_VALID,
 11                     'N/A') AS check_result
 12  FROM validation_output,
 13      (select level as unpivot_row
 14       from dual connect by level <= 4);

    CUSTID CHECK_NAME       CHECK_RESULT
---------- ---------------- ------------
       100 NAME_POPULATED   Y
       101 NAME_POPULATED   N
       100 NAME_VALID       N
       101 NAME_VALID       N
       100 DOB_POPULATED    Y
       101 DOB_POPULATED    N
       100 DOB_VALID        Y
       101 DOB_VALID        N

8 rows selected.

In 11G: Using UNPIVOT

With the UNPIVOT syntax, the same query can be written in a more compact, readable form.

The SQL:

select *
  from validation_output
unpivot
(
  check_result
    for check_name in (
       NAME_POPULATED
      ,NAME_VALID
      ,DOB_POPULATED
      ,DOB_VALID)
);

When run:

SQL> select *
  2    from validation_output
  3  unpivot
  4  (
  5    check_result
  6      for check_name in (
  7         NAME_POPULATED
  8        ,NAME_VALID
  9        ,DOB_POPULATED
 10        ,DOB_VALID)
 11  );

    CUSTID CHECK_NAME       CHECK_RESULT
---------- ---------------- ------------
       100 NAME_POPULATED   Y
       100 NAME_VALID       N
       100 DOB_POPULATED    Y
       100 DOB_VALID        Y
       101 NAME_POPULATED   N
       101 NAME_VALID       N
       101 DOB_POPULATED    N
       101 DOB_VALID        N

8 rows selected.

Notes

1. Aliasing: Column Names Can Be Modified With UNPIVOT

UNPIVOT supports the renaming of column names in the process of converting them to row values.

SQL> select *
  2    from validation_output
  3  unpivot
  4  (
  5    check_result
  6      for check_name in (
  7         NAME_POPULATED as 'NAME_POP'
  8        ,NAME_VALID as 'NAME_VAL'
  9        ,DOB_POPULATED as 'DOB_POP'
 10        ,DOB_VALID as 'DOB_VAL')
 11  );

    CUSTID CHECK_NAME       CHECK_RESULT
---------- ---------------- ------------
       100 NAME_POP         Y
       100 NAME_VAL         N
       100 DOB_POP          Y
       100 DOB_VAL          Y
       101 NAME_POP         N
       101 NAME_VAL         N
       101 DOB_POP          N
       101 DOB_VAL          N

8 rows selected.

2. NULL Handling: Exclude NULL By Default

By default, any NULLs in the data are excluded when you transpose columns as rows using UNPIVOT.

Let’s blank out a value and try it out…

SQL> update validation_output
  2  set name_valid = ''
  3  where custid = 101;

1 row updated.

SQL> select *
  2  from validation_output;

    CUSTID NAME_POPULATED NAME_VALID DOB_POPULATED DOB_VALID
---------- -------------- ---------- ------------- ---------
       100 Y              N          Y             Y
       101 N                         N             N

Now use the same SQL that was run earlier:

SQL> select *
  2    from validation_output
  3  unpivot
  4  (
  5    check_result
  6      for check_name in (
  7         NAME_POPULATED
  8        ,NAME_VALID
  9        ,DOB_POPULATED
 10        ,DOB_VALID)
 11  );

    CUSTID CHECK_NAME       CHECK_RESULT
---------- ---------------- ------------
       100 NAME_POPULATED   Y
       100 NAME_VALID       N
       100 DOB_POPULATED    Y
       100 DOB_VALID        Y
       101 NAME_POPULATED   N
       101 DOB_POPULATED    N
       101 DOB_VALID        N

7 rows selected.

You can see that the SQL did not select the row that had a null value.

To enforce the selection of NULLs, use the clause "INCLUDE NULLS".

SQL> select *
  2    from validation_output
  3  unpivot include nulls
  4  (
  5    check_result
  6      for check_name in (
  7         NAME_POPULATED
  8        ,NAME_VALID
  9        ,DOB_POPULATED
 10        ,DOB_VALID)
 11  );

    CUSTID CHECK_NAME       CHECK_RESULT
---------- ---------------- ------------
       100 NAME_POPULATED   Y
       100 NAME_VALID       N
       100 DOB_POPULATED    Y
       100 DOB_VALID        Y
       101 NAME_POPULATED   N
       101 NAME_VALID
       101 DOB_POPULATED    N
       101 DOB_VALID        N

8 rows selected.

3. DataTypes Must Match!

UNPIVOT expects all columns being used in the UNPIVOT clause to be of the same datatype.

To test this, let’s add a new check PHONE_VALID to the table, but instead of making it a VARCHAR2(1) with Y/N values, define it as NUMBER(1) presumably with 0/1 values.

SQL> alter table validation_output
  2  add (phone_valid number(1));

Table altered.

SQL> desc validation_output
 Name                    Null?    Type
 ----------------------- -------- -----------
 CUSTID                           NUMBER(4)
 NAME_POPULATED                   VARCHAR2(1)
 NAME_VALID                       VARCHAR2(1)
 DOB_POPULATED                    VARCHAR2(1)
 DOB_VALID                        VARCHAR2(1)
 PHONE_VALID                      NUMBER(1)
 

In the UNPIVOT clause, include PHONE_VALID too:

select *
   from validation_output
 unpivot
 (
   check_result
     for check_name in (
        NAME_POPULATED
       ,NAME_VALID
       ,DOB_POPULATED
       ,DOB_VALID
       ,PHONE_VALID)
);

When run:

SQL>  select *
  2     from validation_output
  3   unpivot
  4   (
  5     check_result
  6       for check_name in (
  7          NAME_POPULATED
  8         ,NAME_VALID
  9         ,DOB_POPULATED
 10         ,DOB_VALID
 11         ,PHONE_VALID)
 12  );
       ,PHONE_VALID)
        *
ERROR at line 11:
ORA-01790: expression must have same datatype as
corresponding expression

Oracle throws an error because the datatypes of all columns in the UNPIVOT list are not the same:

ORA-01790: expression must have same datatype as
corresponding expression

Further Reading

A nifty row generation trick that using UNPIVOT: Flexible Row Generator.

The more popular type of transpose – rows to columns – using Oracle’s PIVOT clause.

{ 0 comments… add one now }

Leave a Comment

Previous post:

Next post: