Snowflake SQL Tutorial 60 - UNPIVOT in Snowflake - Why 8 Rows x 4 Columns Gives You 13 Rows, Not 32

Snowflake SQL · Episode 60

In this post we are going to cover UNPIVOT in Snowflake.

UNPIVOT is the mirror of PIVOT. It takes a set of columns and folds them down into rows. So we start with a wide table, one row per customer and one column per order status, and we end up with a long one where the old column names have become data.

We will fold four columns out of eight rows, which looks like it should give us 32 rows back. It gives us 13, and nothing in the syntax tells us why. Once we know why, we will turn the dropped rows back on with two extra words, prove that PIVOT and UNPIVOT really are inverses, look at what the new name column holds, and meet the one error UNPIVOT will always give you on a real table.

Every query below was run on a real Snowflake account, and every result you see is the output it gave back, including both error messages.

Episode 60 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.

Let's create some sample data

UNPIVOT needs a wide table to work on, and the one we will use is the table Episode 59 finished on. One row per customer, one column per order status.

It is not a stored table. It is the PIVOT from Episode 59 wrapped in a CTE called WIDE, which every query on this page reuses. Run the script once and you can paste any query here straight into a worksheet.

Sample data — run once, then build WIDE
-- ORDERS, EMPLOYEES and DEPARTMENTS come from the setup script this course
-- uses throughout. Run it once; every episode reuses the same tables.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

-- The wide table every query in Part 1 starts from is not stored anywhere -
-- it is built by the PIVOT from Episode 59 and wrapped in a CTE, so you can
-- paste any query on this page straight into a worksheet.
WITH WIDE AS (
    SELECT *
      FROM (SELECT CUSTOMER_ID, STATUS, AMOUNT FROM ORDERS)
      PIVOT (SUM(AMOUNT) FOR STATUS IN ('SHIPPED','PENDING','CANCELLED','RETURNED'))
        AS P (CUSTOMER_ID, SHIPPED, PENDING, CANCELLED, RETURNED)
)
SELECT * FROM WIDE ORDER BY CUSTOMER_ID;
Query — the input to everything that follows
WITH WIDE AS (
  SELECT * FROM (SELECT CUSTOMER_ID, STATUS, AMOUNT FROM ORDERS)
  PIVOT (SUM(AMOUNT) FOR STATUS IN ('SHIPPED','PENDING','CANCELLED','RETURNED'))
    AS P (CUSTOMER_ID, SHIPPED, PENDING, CANCELLED, RETURNED)
)
SELECT * FROM WIDE ORDER BY CUSTOMER_ID;
Result8 rows · 5 columns
CUSTOMER_IDSHIPPEDPENDINGCANCELLEDRETURNED
10161660.00NULLNULLNULL
1024230.50NULLNULL1830.00
10314135.25NULL1500.00NULL
1046826.25NULL2250.00NULL
10529665.006900.00NULLNULL
1063120.00640.00NULLNULL
10756800.00NULLNULLNULL
10915870.00NULLNULLNULL

Five columns. CUSTOMER_ID, which identifies the row, and four measure columns holding money: SHIPPED, PENDING, CANCELLED and RETURNED. Those four are the ones we are about to fold up.

Now let's count the cells before we fold anything

Eight rows across four measure columns is 32 cells. But most of those cells are empty, and that is the whole post. Read the wide table down and count the cells that actually hold a number. Customer 101 has one, 102 has two, 103 two, 104 two, 105 two, 106 two, 107 one and 109 one.

That is 13. Let's get Snowflake to count the same three figures, so you do not have to take my word for it.

Query — the three numbers side by side
WITH WIDE AS (
  SELECT * FROM (SELECT CUSTOMER_ID, STATUS, AMOUNT FROM ORDERS)
  PIVOT (SUM(AMOUNT) FOR STATUS IN ('SHIPPED','PENDING','CANCELLED','RETURNED'))
    AS P (CUSTOMER_ID, SHIPPED, PENDING, CANCELLED, RETURNED)
)
SELECT COUNT(*) AS WIDE_ROWS,
       COUNT(*) * 4 AS CELLS_IF_EVERY_COLUMN_COUNTED,
       COUNT(SHIPPED) + COUNT(PENDING) + COUNT(CANCELLED) + COUNT(RETURNED)
         AS CELLS_THAT_ARE_NOT_NULL
FROM WIDE;
Result1 row · 3 columns
WIDE_ROWSCELLS_IF_EVERY_COLUMN_COUNTEDCELLS_THAT_ARE_NOT_NULL
83213

Remember the 13. It is not a fact about UNPIVOT yet. It is a fact about this grid, counted before we folded anything. In a moment it turns out to predict exactly what UNPIVOT returns.

Why so many holes

A PIVOT produces a NULL wherever a customer had no orders in that status at all. There is nothing wrong with the data. A sparse wide table is the normal outcome of pivoting anything real, and the sparser it is, the bigger the gap between 32 and 13 gets.

Now let's write the UNPIVOT

UNPIVOT attaches to the table in FROM and takes three things, in this order.

  1. The value column, which is the new column the numbers will land in. Here it is TOTAL.
  2. The word FOR, then the name column, which is the new column the old column names will land in. Here it is STATUS.
  3. IN (...), which is the list of columns being folded up.
Query
WITH WIDE AS (
  SELECT * FROM (SELECT CUSTOMER_ID, STATUS, AMOUNT FROM ORDERS)
  PIVOT (SUM(AMOUNT) FOR STATUS IN ('SHIPPED','PENDING','CANCELLED','RETURNED'))
    AS P (CUSTOMER_ID, SHIPPED, PENDING, CANCELLED, RETURNED)
)
SELECT * FROM WIDE
UNPIVOT (TOTAL FOR STATUS IN (SHIPPED, PENDING, CANCELLED, RETURNED))
ORDER BY CUSTOMER_ID, STATUS;
Result13 rows · 3 columns
CUSTOMER_IDSTATUSTOTAL
101SHIPPED61660.00
102RETURNED1830.00
102SHIPPED4230.50
103CANCELLED1500.00
103SHIPPED14135.25
104CANCELLED2250.00
104SHIPPED6826.25
105PENDING6900.00
105SHIPPED29665.00
106PENDING640.00
106SHIPPED3120.00
107SHIPPED56800.00
109SHIPPED15870.00

Three columns back. The key column that was not folded, plus the two new ones. And 13 rows, which is the same 13 cells we counted in the wide table, each one now a row of its own.

If you were expecting 32, that is the arithmetic this post is named after. Let's put both numbers against the actual result.

Query
WITH WIDE AS (
  SELECT * FROM (SELECT CUSTOMER_ID, STATUS, AMOUNT FROM ORDERS)
  PIVOT (SUM(AMOUNT) FOR STATUS IN ('SHIPPED','PENDING','CANCELLED','RETURNED'))
    AS P (CUSTOMER_ID, SHIPPED, PENDING, CANCELLED, RETURNED)
), LONG AS (
  SELECT * FROM WIDE
  UNPIVOT (TOTAL FOR STATUS IN (SHIPPED, PENDING, CANCELLED, RETURNED))
)
SELECT (SELECT COUNT(*) FROM WIDE)     AS WIDE_ROWS,
       (SELECT COUNT(*) FROM WIDE) * 4 AS ROWS_IF_NULLS_KEPT,
       (SELECT COUNT(*) FROM LONG)     AS ROWS_ACTUALLY_RETURNED;
Result1 row · 3 columns
WIDE_ROWSROWS_IF_NULLS_KEPTROWS_ACTUALLY_RETURNED
83213
The behaviour that catches everybody

UNPIVOT excludes NULLs by default, and nothing in the syntax we just wrote says so. Every empty cell in the wide table is silently dropped instead of becoming a row. The row count you get is the number of populated cells, not rows × columns.

This matters beyond row counts. If we unpivot a wide table to load a long one, the missing rows are the difference between this customer had no cancelled orders and this customer had cancelled orders worth nothing. By default that distinction is made for us, silently, in favour of the first one.

Can we keep the empty cells?

Yes. If we want the empty cells as rows, we say so. Two extra words go between UNPIVOT and the opening bracket. Nothing else about the statement changes.

Query
WITH WIDE AS (
  SELECT * FROM (SELECT CUSTOMER_ID, STATUS, AMOUNT FROM ORDERS)
  PIVOT (SUM(AMOUNT) FOR STATUS IN ('SHIPPED','PENDING','CANCELLED','RETURNED'))
    AS P (CUSTOMER_ID, SHIPPED, PENDING, CANCELLED, RETURNED)
)
SELECT * FROM WIDE
UNPIVOT INCLUDE NULLS (TOTAL FOR STATUS IN (SHIPPED, PENDING, CANCELLED, RETURNED))
ORDER BY CUSTOMER_ID, STATUS;
Result32 rows · 3 columns
CUSTOMER_IDSTATUSTOTAL
101CANCELLEDNULL
101PENDINGNULL
101RETURNEDNULL
101SHIPPED61660.00
102CANCELLEDNULL
102PENDINGNULL
102RETURNED1830.00
102SHIPPED4230.50
103CANCELLED1500.00
103PENDINGNULL
103RETURNEDNULL
103SHIPPED14135.25
104CANCELLED2250.00
104PENDINGNULL
104RETURNEDNULL
104SHIPPED6826.25
105CANCELLEDNULL
105PENDING6900.00
105RETURNEDNULL
105SHIPPED29665.00
106CANCELLEDNULL
106PENDING640.00
106RETURNEDNULL
106SHIPPED3120.00
107CANCELLEDNULL
107PENDINGNULL
107RETURNEDNULL
107SHIPPED56800.00
109CANCELLEDNULL
109PENDINGNULL
109RETURNEDNULL
109SHIPPED15870.00

32 rows. That is 8 × 4, every cell of the grid, in or out. Customer 101 now contributes four rows instead of one, three of them carrying a NULL TOTAL.

Let's count the two spellings in a single statement.

Query
WITH WIDE AS (
  SELECT * FROM (SELECT CUSTOMER_ID, STATUS, AMOUNT FROM ORDERS)
  PIVOT (SUM(AMOUNT) FOR STATUS IN ('SHIPPED','PENDING','CANCELLED','RETURNED'))
    AS P (CUSTOMER_ID, SHIPPED, PENDING, CANCELLED, RETURNED)
)
SELECT
  (SELECT COUNT(*) FROM WIDE
     UNPIVOT (T FOR S IN (SHIPPED, PENDING, CANCELLED, RETURNED)))
     AS DEFAULT_EXCLUDE_NULLS,
  (SELECT COUNT(*) FROM WIDE
     UNPIVOT INCLUDE NULLS (T FOR S IN (SHIPPED, PENDING, CANCELLED, RETURNED)))
     AS WITH_INCLUDE_NULLS;
Result1 row · 2 columns
DEFAULT_EXCLUDE_NULLSWITH_INCLUDE_NULLS
1332

19 rows separate those two numbers, and they are the 19 empty cells of the wide table. Exclude is the default. Include is the opt-in. That is the entire behaviour, and there is no third option.

Choosing between them

Which one you want depends on what the absent value means. For a long fact table to aggregate later, the default is almost always right. A row holding nothing is a row every later SUM, COUNT and join has to step over. For a dense matrix where every combination has to be present, such as a forecast grid or a report that needs a cell for every status, INCLUDE NULLS is the one you want.

Is UNPIVOT really the inverse of PIVOT?

UNPIVOT is the inverse of PIVOT is the kind of claim that is easy to repeat and easy to half-believe. It is testable. We pivot the orders, unpivot the result, and subtract the original long form with MINUS. If the round trip gave back exactly what went in, nothing is left over.

Query — round trip MINUS original
WITH WIDE AS (
  SELECT * FROM (SELECT CUSTOMER_ID, STATUS, AMOUNT FROM ORDERS)
  PIVOT (SUM(AMOUNT) FOR STATUS IN ('SHIPPED','PENDING','CANCELLED','RETURNED'))
    AS P (CUSTOMER_ID, SHIPPED, PENDING, CANCELLED, RETURNED)
)
SELECT CUSTOMER_ID, STATUS, TOTAL
FROM WIDE
UNPIVOT (TOTAL FOR STATUS IN (SHIPPED, PENDING, CANCELLED, RETURNED))
MINUS
SELECT CUSTOMER_ID, STATUS, SUM(AMOUNT)
FROM ORDERS
GROUP BY CUSTOMER_ID, STATUS;
Result0 rows · 3 columns
CUSTOMER_IDSTATUSTOTAL

Empty. No row survived the subtraction, so every row the round trip produced was already in the long form.

One direction is not a proof

MINUS only looks one way. An empty result tells us there is nothing on the left that is missing from the right. It would be just as empty if the right-hand side had extra rows. So let's run it the other way round, with the same two queries swapped.

Query — original MINUS round trip
WITH WIDE AS (
  SELECT * FROM (SELECT CUSTOMER_ID, STATUS, AMOUNT FROM ORDERS)
  PIVOT (SUM(AMOUNT) FOR STATUS IN ('SHIPPED','PENDING','CANCELLED','RETURNED'))
    AS P (CUSTOMER_ID, SHIPPED, PENDING, CANCELLED, RETURNED)
)
SELECT CUSTOMER_ID, STATUS, SUM(AMOUNT)
FROM ORDERS
GROUP BY CUSTOMER_ID, STATUS
MINUS
SELECT CUSTOMER_ID, STATUS, TOTAL
FROM WIDE
UNPIVOT (TOTAL FOR STATUS IN (SHIPPED, PENDING, CANCELLED, RETURNED));
Result0 rows · 3 columns
CUSTOMER_IDSTATUSSUM(AMOUNT)

Also empty. Neither side has a row the other lacks, so the two results are the same set. Now it is proved.

Why the default matters here

That round trip only closes because the default dropped the NULLs. The long form never contained a row for a status a customer never used. With INCLUDE NULLS the unpivoted side would carry 19 rows the original does not have, and the first MINUS would return all 19 of them.

What does the name column actually hold?

The name column is the interesting half of the output, because its values did not exist as data a moment ago. They were column headers. Let's group by it and see what it contains.

Query
WITH WIDE AS (
  SELECT * FROM (SELECT CUSTOMER_ID, STATUS, AMOUNT FROM ORDERS)
  PIVOT (SUM(AMOUNT) FOR STATUS IN ('SHIPPED','PENDING','CANCELLED','RETURNED'))
    AS P (CUSTOMER_ID, SHIPPED, PENDING, CANCELLED, RETURNED)
)
SELECT STATUS, COUNT(*) AS N, SUM(TOTAL) AS TOTAL
FROM WIDE
UNPIVOT (TOTAL FOR STATUS IN (SHIPPED, PENDING, CANCELLED, RETURNED))
GROUP BY STATUS
ORDER BY STATUS;
Result4 rows · 3 columns
STATUSNTOTAL
CANCELLED23750.00
PENDING27540.00
RETURNED11830.00
SHIPPED8192307.00

Four rows, one per column that was folded in, and the names are the old headers spelled exactly as they were. CANCELLED, PENDING, RETURNED, SHIPPED. The N column adds up to 13, which is the same 13 again. This grid is those 13 rows regrouped, nothing more.

Its type is worth one query of its own, because people assume it is something special.

Query
WITH WIDE AS (
  SELECT * FROM (SELECT CUSTOMER_ID, STATUS, AMOUNT FROM ORDERS)
  PIVOT (SUM(AMOUNT) FOR STATUS IN ('SHIPPED','PENDING','CANCELLED','RETURNED'))
    AS P (CUSTOMER_ID, SHIPPED, PENDING, CANCELLED, RETURNED)
)
SELECT DISTINCT TYPEOF(STATUS::VARIANT) AS NAME_COLUMN_TYPE
FROM WIDE
UNPIVOT (TOTAL FOR STATUS IN (SHIPPED, PENDING, CANCELLED, RETURNED));
Result1 row · 1 column
NAME_COLUMN_TYPE
VARCHAR

VARCHAR. It is an ordinary string column holding the identifiers as text, which is why we can filter it, join on it and group by it like any other column.

What you are counting changed

N here counts customers, not orders. Each row of the wide table was one customer, so each unpivoted row is one customer’s total for one status. Two customers carry pending money between them. How many pending orders that is, this grid cannot tell us. The number got smaller because the question changed, not because anything disagrees.

Now let's fold two columns that are both numbers

Everything so far folded four columns that came out of the same SUM(AMOUNT), so they were identically typed by construction. On a real table they will not be, and this is the error you will meet.

EMPLOYEES has SALARY and COMMISSION_PCT. Both are numeric. Folding them into one measure column is the obvious thing to write.

Query — this one fails
SELECT EMP_ID, FIRST_NAME, MEASURE_NAME, MEASURE_VALUE
FROM (SELECT EMP_ID, FIRST_NAME, SALARY, COMMISSION_PCT FROM EMPLOYEES)
UNPIVOT (MEASURE_VALUE FOR MEASURE_NAME IN (SALARY, COMMISSION_PCT))
ORDER BY EMP_ID, MEASURE_NAME;
Query failed — Snowflake returned an error
001090 (22000): SQL compilation error:
The type of column 'COMMISSION_PCT' conflicts with the type of other columns in the UNPIVOT list.

Both columns are numbers and Snowflake still refuses. The message says the types conflict without saying how. So let's stop guessing and ask the catalogue.

Query — the actual declared types
SELECT COLUMN_NAME, DATA_TYPE, NUMERIC_PRECISION, NUMERIC_SCALE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'EMPLOYEES'
  AND COLUMN_NAME IN ('SALARY', 'COMMISSION_PCT')
ORDER BY COLUMN_NAME;
Result2 rows · 4 columns
COLUMN_NAMEDATA_TYPENUMERIC_PRECISIONNUMERIC_SCALE
COMMISSION_PCTNUMBER42
SALARYNUMBER102

There it is. COMMISSION_PCT is NUMBER(4,2) and SALARY is NUMBER(10,2). Same DATA_TYPE, same scale, different precision. And the same data type means the same precision and scale too.

The fix is a cast in the inner SELECT

Every column folded into one value column has to arrive as one type, so let's give them one. We cast both in the subquery UNPIVOT reads from, not outside it.

Query
SELECT EMP_ID, FIRST_NAME, MEASURE_NAME, MEASURE_VALUE
FROM (SELECT EMP_ID, FIRST_NAME,
             SALARY::NUMBER(12,2)         AS SALARY,
             COMMISSION_PCT::NUMBER(12,2) AS COMMISSION_PCT
      FROM EMPLOYEES)
UNPIVOT (MEASURE_VALUE FOR MEASURE_NAME IN (SALARY, COMMISSION_PCT))
ORDER BY EMP_ID, MEASURE_NAME;
Result11 rows · 4 columns
EMP_IDFIRST_NAMEMEASURE_NAMEMEASURE_VALUE
1AishaSALARY118000.00
2MarcusSALARY96500.00
3LenaCOMMISSION_PCT0.05
3LenaSALARY104000.00
4TomasSALARY87250.00
5PriyaCOMMISSION_PCT0.10
5PriyaSALARY96500.00
6DanielSALARY131000.00
7SofiaCOMMISSION_PCT0.08
7SofiaSALARY71400.00
8GraceSALARY92000.00

11 rows, and they are worth reading. Every employee contributed a SALARY row, which is eight of them. Only Lena, Priya and Sofia contributed a COMMISSION_PCT row, because only those three have one. 8 + 3 = 11, and the five employees with no commission simply have no second row.

Query — the same arithmetic in one row
SELECT
  (SELECT COUNT(*) FROM EMPLOYEES) AS EMPLOYEES,
  (SELECT COUNT(*) FROM EMPLOYEES) * 2 AS ROWS_IF_NULLS_KEPT,
  (SELECT COUNT(*) FROM (SELECT EMP_ID,
                                SALARY::NUMBER(12,2)         AS SALARY,
                                COMMISSION_PCT::NUMBER(12,2) AS COMMISSION_PCT
                         FROM EMPLOYEES)
     UNPIVOT (V FOR N IN (SALARY, COMMISSION_PCT))) AS ROWS_RETURNED,
  (SELECT COUNT(COMMISSION_PCT) FROM EMPLOYEES) AS NON_NULL_COMMISSIONS;
Result1 row · 4 columns
EMPLOYEESROWS_IF_NULLS_KEPTROWS_RETURNEDNON_NULL_COMMISSIONS
816113

Which is the same NULL rule we met on the wide table, this time on a table nobody pivoted. The row count is the number of populated cells, and the default never mentions the ones it dropped.

And here is the same rule where nobody argues with it

The precision conflict feels unfair, because both columns are numbers. So let's see the same rule where it is obvious. DEPARTMENTS has a numeric BUDGET, and folding that on its own is fine.

Query
SELECT DEPT_ID, DEPT_NAME, MEASURE_NAME, MEASURE_VALUE
FROM (SELECT DEPT_ID, DEPT_NAME, BUDGET FROM DEPARTMENTS)
UNPIVOT (MEASURE_VALUE FOR MEASURE_NAME IN (BUDGET))
ORDER BY DEPT_ID;
Result4 rows · 4 columns
DEPT_IDDEPT_NAMEMEASURE_NAMEMEASURE_VALUE
10EngineeringBUDGET2400000.00
20FinanceBUDGET1100000.00
30MarketingBUDGET750000.00
40OperationsBUDGET640000.00

Now let's add LOCATION, which is text, to the same IN list.

Query — this one fails too
SELECT *
FROM (SELECT DEPT_ID, DEPT_NAME, LOCATION, BUDGET FROM DEPARTMENTS)
UNPIVOT (V FOR N IN (LOCATION, BUDGET))
ORDER BY DEPT_ID;
Query failed — Snowflake returned an error
001090 (22000): SQL compilation error:
The type of column 'BUDGET' conflicts with the type of other columns in the UNPIVOT list.

The same error code, and this time no surprise at all. A city name and an amount of money cannot share one column, so Snowflake will not invent a type that holds both.

If you really must mix them

If you genuinely need text and numbers in one folded column, cast them all to VARCHAR in the inner SELECT, and accept that the result is a column of strings you can no longer total. UNPIVOT is for folding up columns that mean the same kind of thing. If yours do not, it is usually the wrong tool.

Key points

  • UNPIVOT turns columns into rows: you name the value column, then FOR and the name column, then IN (...) with the columns to fold up.
  • It drops NULLs by default, and nothing in the syntax says so. The row count you get is the number of populated cells, not rows × folded columns.
  • UNPIVOT INCLUDE NULLS (...) is the opt-in, and it returns every cell of the grid including the empty ones.
  • The name column comes back as an ordinary VARCHAR holding the old column names as text, so you can filter, group and join on it.
  • Every column in the IN list must have exactly the same type, precision and scale included. NUMBER(4,2) and NUMBER(10,2) are not the same type.
  • The fix is a cast in the inner SELECT, so that all the folded columns arrive at UNPIVOT already identical.
  • PIVOT and UNPIVOT are genuine inverses here, proved with MINUS in both directions, but only because the default dropped the cells the long form never had.
  • When you count the unpivoted rows, check what one row of the wide table represented. Folding a per-customer grid gives you customers, not orders.
Next in this series

In the next post we will cover SAMPLE. Taking a random slice of a table, why the same statement returns a different number of rows each time, and the one place a sample will quietly lie to you.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.34.101) running the demo script for this episode, including both error panes. The ORDERS, EMPLOYEES and DEPARTMENTS data is invented demo data created by that script. No performance or benchmark claims are made anywhere in this series. Clause behaviour follows the Snowflake UNPIVOT documentation.

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.