I’ve recently encountered a very interesting question on Stack Overflow by an unnamed user. The question was about generating a table of the following form in Oracle, using a table valued function:
The logic that should be implemented for the COUNT column is the following:
TEST1: count of employees whose sal < 10000
TEST2: count of employees whose dept > 10
TEST3: count of employees whose hiredate > (SYSDATE-60)
TEST4: count of employees whose grade = 1
Challenge accepted!
For this exercise, let’s assume the following table:
CREATE TABLE employees (
id NUMBER(18) NOT NULL PRIMARY KEY,
sal NUMBER(18, 2) NOT NULL,
dept NUMBER(18) NOT NULL,
hiredate DATE NOT NULL,
grade NUMBER(18) NOT NULL
);
INSERT INTO employees
VALUES (1, 10000, 1, SYSDATE , 1);
INSERT INTO employees
VALUES (2, 9000, 5, SYSDATE - 10, 1);
INSERT INTO employees
VALUES (3, 11000, 13, SYSDATE - 30, 2);
INSERT INTO employees
VALUES (4, 10000, 12, SYSDATE - 80, 2);
INSERT INTO employees
VALUES (5, 8000, 7, SYSDATE - 90, 1);
How to calculate the COUNT values
In a first step, we’re going to look into how to best calculate the COUNT values. The simplest way is to calculate the values in individual columns, not rows. SQL newbies will probably resort to a canonical solution using nested SELECTs, which is very bad for performance reasons:
SELECT
(SELECT COUNT(*) FROM employees
WHERE sal < 10000) AS test1,
(SELECT COUNT(*) FROM employees
WHERE dept > 10) AS test2,
(SELECT COUNT(*) FROM employees
WHERE hiredate > (SYSDATE - 60)) AS test3,
(SELECT COUNT(*) FROM employees
WHERE grade = 1) AS test4
FROM dual;
Why is the query not optimal? There are four table accesses to find all the data:
If you add an index to each individual column being filtered, chances are at least to optimise individual subqueries, but for these kinds of reports, the occasional full table scan is perfectly fine, especially if you aggregate a lot of data.
Even if not optimal in speed, the above yields the correct result:
Few people are aware of the fact that aggregate functions only aggregate non-NULL values. This has no effect, when you write COUNT(*), but when you pass an expression to the COUNT(expr) function, this becomes much more interesting!
The idea here is that you use a CASE expression that transforms each predicate’s TRUE evaluation into a non-NULL value, an the FALSE (or NULL) evaluation into NULL. The following query illustrates this approach
SELECT
COUNT(CASE WHEN sal < 10000 THEN 1 END)
AS test1,
COUNT(CASE WHEN dept > 10 THEN 1 END)
AS test2,
COUNT(CASE WHEN hiredate > (SYSDATE-60) THEN 1 END)
AS test3,
COUNT(CASE WHEN grade = 1 THEN 1 END)
AS test4
FROM employees;
SELECT
COUNT(*) FILTER (WHERE sal < 10000)
AS test1,
COUNT(*) FILTER (WHERE dept > 10)
AS test2,
COUNT(*) FILTER (WHERE hiredate > (SYSDATE - 60))
AS test3,
COUNT(*) FILTER (WHERE grade = 1)
AS test4
FROM employees;
This is useful when you want to cleanly separate the FILTER() criteria from any other expression that you want to use for aggregating. E.g. when calculating a SUM().
In any case, the query now has to hit the table only once. The aggregation can then be performed entirely in memory.
This is always better than the previous approach, unless you have an index for every aggregation!
OK. Now how to get the results in rows?
The question on Stack Overflow wanted a result with TESTn values being put in individual rows, not columns.
Again, there’s a canonical, not so performant approach to do this with UNION ALL:
SELECT
'TEST1' AS Description,
COUNT(*) AS COUNT
FROM employees WHERE sal < 10000
UNION ALL
SELECT
'TEST2',
COUNT(*)
FROM employees WHERE dept > 10
UNION ALL
SELECT
'TEST3',
COUNT(*)
FROM employees WHERE hiredate > (SYSDATE - 60)
UNION ALL
SELECT
'TEST4',
COUNT(*)
FROM employees WHERE grade = 1
This approach is more or less equivalent to the nested selects approach, except for the column / row transposition (“unpivoting”). And the plan is also very similar:
Transposition = (un)pivoting
Notice how I used the term “transpose”. That’s what we did, and it has a name: (un)pivoting. Not only does it have a name, but this feature is also supported out of the box in Oracle and SQL Server via the PIVOT and UNPIVOT keywords that can be placed after table references.
PIVOT transposes rows into columns
UNPIVOT transposes columns back into rows
So, we’ll take the original, optimal solution, and transpose that with UNPIVOT
SELECT *
FROM (
SELECT
COUNT(CASE WHEN sal < 10000 THEN 1 END)
AS test1,
COUNT(CASE WHEN dept > 10 THEN 1 END)
AS test2,
COUNT(CASE WHEN hiredate > (SYSDATE-60) THEN 1 END)
AS test3,
COUNT(CASE WHEN grade = 1 THEN 1 END)
AS test4
FROM employees
) t
UNPIVOT (
count FOR description IN (
"TEST1", "TEST2", "TEST3", "TEST4"
)
)
All we need to do is wrap the original query in a derived table t (i.e. an inline SELECT in the FROM clause), and then “UNPIVOT” that table t, generating the count and description columns. The result is, again:
The execution plan is still optimal. All the action is happening in memory.
Conclusion
PIVOT and UNPIVOT are very useful tools for reporting and reorganising data. There are many use-cases like the above, where you want to re-organise some aggregations. Other use-cases include settings or properties tables that implement an entity attribute value model, and you want to transform attributes from rows to columns (PIVOT), or from columns to rows (UNPIVOT)
Intrigued? Read on about PIVOT here:
4 thoughts on “Impress Your Coworkers by Using SQL UNPIVOT!”
Shouldn’t the UNPIVOT query be like this instead?
SELECT *
FROM (
SELECT
'TEST1' AS Description,
COUNT(*) AS COUNT
FROM employees WHERE sal < 10000
UNION ALL
SELECT
'TEST2',
COUNT(*)
FROM employees WHERE dept > 10
UNION ALL
SELECT
'TEST3',
COUNT(*)
FROM employees WHERE hiredate > (SYSDATE - 60)
UNION ALL
SELECT
'TEST4',
COUNT(*)
FROM employees WHERE grade = 1
) t
UNPIVOT (
count FOR description IN (
"TEST1", "TEST2", "TEST3", "TEST4"
)
)
The one on your post is missing the `Description` and the `COUNT` aliases.
I don’t get it. The inner-table only has 4 columns: test1, test2, test3 and test4. How does the UNPIVOT know that the description is in those columns because it has no notion of any `description` column.
Shouldn’t the UNPIVOT query be like this instead?
The one on your post is missing the `Description` and the `COUNT` aliases.
I invite you to observe with me the miracles of UNPIVOT in action:
Also, please don’t go ahead with that
UNION ALL
approach. I explained in the article why it’s not optional…I don’t get it. The inner-table only has 4 columns: test1, test2, test3 and test4. How does the UNPIVOT know that the description is in those columns because it has no notion of any `description` column.
How does it not have any notion of a “description” column? I named it “description” in the UNPIVOT clause:
With:
count
columndescription
columnNow, this being intuitive or not is another question ;)