Easily transpose columns as rows in Oracle 11G+, with the UNPIVOT clause in 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.
