In relational data analysis, sometimes we want to convert the table columns to rows and rows to columns. For instance, the following table (Table A) shows the score by course (column) for each student (row). In this case, STUDENT is the key field for a record, The courses: ARTS, CHEMISTRY and PHYSICS are the attributes of a STUDENTrecord, with scores as field values respectively.
Table A:
| STUDENT | ARTS | CHEMISTRY | PHYSICS |
| A001 | 65 | 95 | 90 |
| A002 | 88 | 70 | 85 |
| A003 | 92 | 93 | 67 |
| A004 | 82 | 89 | 77 |
The resulting clustered bar chart looks like this:
On some occasions, we want to analyze the scores by student (column) for each course (row), as shown in Table B. Now the key field is COURSE, and the students: A001, A002, A003, A004 become attributes of a COURSE record.
Table B:
| COURSE | A001 | A002 | A003 | A004 |
| Arts | 65 | 88 | 92 | 82 |
| Chemistry | 95 | 70 | 93 | 89 |
| Physics | 90 | 85 | 67 | 77 |
The resulting clustered bar chart is like this:
Comparing to Table A, Table B is the transpose of Table A. The question is: how to convert from Table A to Table B? There are many approaches to achieve this goal. Today we are introducing the most compact and easiest way—using UNPIVOT and PIVOT functions in one script.
UNPIVOT Operator
UNPIVOT operator converts columns to rows. Below is the syntax:
SELECT [columns]
FROM [table “” not found /]
UNPIVOT [INCLUDE | EXCLUDE NULLS]
(
unpivot_clause
for_clause
in_clause
);
In this syntax:
- The optional INCLUDE | EXCLUDE NULLS clause allows you to include or exclude null-valued rows.
- The unpivot_clause specifies a new column name that represents the measured values after unpivot.
- The unpivot_for_clause specifies a new column name that will define the initial column names.
- The unpivot_in_clause contains the names of all columns as row values after unpivoted.
PIVOT Operator
PIVOT operator converts rows to columns. Below is the syntax:
SELECT [columns]
FROM [table “” not found /]
PIVOT
(
pivot_clause
for_clause
in_clause
);
In this syntax:
- pivot_clause specifies the column(s) that you want to aggregate.
- The pivot_clause performs an implicitly GROUP BY based on the values provided by the pivot_in_clause and non-pivoted column(s).
- The reason for aggregation is because it is possible there are multiple rows with the same value as the transposed column name.
- pivot_for_clause specifies the column that you want to group or pivot.
- pivot_in_clause defines a filter for column(s) in the pivot_for_clause. Each distinct value in the pivot_in_clause will be converted into a separate column.
For more details about the syntax of UNPIVOT and PIVOT, please refer to the following two articles:
Let’s see how these two operators work in our examples. First, we build a quick data view called “student_score” to simulate the Table A
SELECT ‘A001’ student, 65 arts, 95 chemistry, 90 physics FROM dual
UNION ALL
SELECT ‘A002’ student, 88 arts, 70 chemistry, 85 physics FROM dual
UNION ALL
SELECT ‘A003’ student, 92 arts, 93 chemistry, 67 physics FROM dual
UNION ALL
SELECT ‘A004’ student, 82 arts, 89 chemistry, 77 physics FROM dual
After applying the UNPIVOT clause, the SQL script looks like this:
WITH student_score AS
(
SELECT ‘A001’ student, 65 arts, 95 chemistry, 90 physics FROM dual
UNION ALL
SELECT ‘A002’ student, 88 arts, 70 chemistry, 85 physics FROM dual
UNION ALL
SELECT ‘A003’ student, 92 arts, 93 chemistry, 67 physics FROM dual
UNION ALL
SELECT ‘A004’ student, 82 arts, 89 chemistry, 77 physics FROM dual
)
SELECT * from student_score
UNPIVOT
(
score –unpivot_clause
FOR course –for_clause
IN ( –in_clause
arts as ‘Arts’,
chemistry as ‘Chemistry’,
physics as ‘Physics’
)
)
The result set after execution is:
| STUDENT | COURSE | SCORE |
| A001 | Arts | 65 |
| A001 | Chemistry | 95 |
| A001 | Physics | 90 |
| A002 | Arts | 88 |
| A002 | Chemistry | 70 |
| A002 | Physics | 85 |
| A003 | Arts | 92 |
| A003 | Chemistry | 93 |
| A003 | Physics | 67 |
| A004 | Arts | 82 |
| A004 | Chemistry | 89 |
| A004 | Physics | 77 |
The course column names become value of the Course column after the UNPIVOT operation.
Let’s see how the table will look like if we apply the PIVOT clause right after the UNPIVOT. There are two options to apply the PIVOT clause after the UNPIVOT clause. However, the result will be different.
Option 1: Aggregate for “course”.
WITH student_score AS
(
SELECT ‘A001’ student, 65 arts, 95 chemistry, 90 physics FROM dual
UNION ALL
SELECT ‘A002’ student, 88 arts, 70 chemistry, 85 physics FROM dual
UNION ALL
SELECT ‘A003’ student, 92 arts, 93 chemistry, 67 physics FROM dual
UNION ALL
SELECT ‘A004’ student, 82 arts, 89 chemistry, 77 physics FROM dual
)
SELECT * from student_score
UNPIVOT
(
score –unpivot_clause
FOR course –for_clause
IN ( –in_clause
arts as ‘Arts’,
chemistry as ‘Chemistry’,
physics as ‘Physics’
)
)
PIVOT
(
SUM (score) — pivot_clause
FOR course — pivot_ for_clause
IN ( — pivot_ in_clause
‘Arts’ as arts,
‘Chemistry’ as chemistry,
‘Physics’ as physics
)
)
The result set is
| STUDENT | ARTS | CHEMISTRY | PHYSICS |
| A001 | 65 | 95 | 90 |
| A002 | 88 | 70 | 85 |
| A003 | 92 | 93 | 67 |
| A004 | 82 | 89 | 77 |
Have readers discovered the interim table is rolled back to Table A? This is self-explanatory, since the UNPIVOT and PIVOT clauses are sharing the same fields, so there are offset to each other.
Option 2: Aggregate for “student”.
WITH student_score AS
(
SELECT ‘A001’ student, 65 arts, 95 chemistry, 90 physics FROM dual
UNION ALL
SELECT ‘A002’ student, 88 arts, 70 chemistry, 85 physics FROM dual
UNION ALL
SELECT ‘A003’ student, 92 arts, 93 chemistry, 67 physics FROM dual
UNION ALL
SELECT ‘A004’ student, 82 arts, 89 chemistry, 77 physics FROM dual
)
SELECT * from student_score
UNPIVOT
(
score –unpivot_clause
FOR course –for_clause
IN ( –in_clause
arts as ‘Arts’,
chemistry as ‘Chemistry’,
physics as ‘Physics’
)
)
PIVOT
(
SUM (score) — pivot_clause
FOR student — pivot_ for_clause
IN ( — pivot_ in_clause
‘A001’ as A001,
‘A002’ as A002,
‘A003’ as A003,
‘A004’ as A004
)
)
The result set is
| COURSE | A001 | A002 | A003 | A004 |
| Arts | 65 | 88 | 92 | 82 |
| Chemistry | 95 | 70 | 93 | 89 |
| Physics | 90 | 85 | 67 | 77 |
Comparing to Table A, the columns become rows and rows become columns. This is the transpose of Table A, which is our desired result!
Summary:
PIVOT and UNPIVOT in SQL are two relational operators to convert a table into each other. Using PIVOT and UNPIVOT in a SQL script properly can facilitate data analytics or multidimensional reporting. With easy-understanding and compact script structure, it also improves the SQL development lifecycle with better efficiency and fewer errors.