UNPIVOT INCLUDE NULLS in BigQuery to Keep NULL Rows

While solving a bug I was reminded again that, when UNPIVOTing, rows with NULL values are excluded. Fine.

But it turns out we have the option to specify the INCLUDE NULLS with UNPIVOT, thus allowing us to keep those rows in the result set.

Let's look at an example.

This is how it would look if UNPIVOTed as usual:

SELECT 
    measurement_date, 
    value, 
    measurement 
FROM input
UNPIVOT INCLUDE NULLS (value FOR measurement IN (water_level, temperature, pressure))

As you notice, we don't have the rows where the measurement values are NULL.

How can we fix it? Let's use UNPIVOT in conjunction with INCLUDE NULLS.

SELECT 
    measurement_date, 
    value, 
    measurement 
FROM input
UNPIVOT INCLUDE NULLS (value FOR measurement IN (water_level, temperature, pressure))

Voila! The NULL entries are here now.

Found it useful? Subscribe to my Analytics newsletter at notjustsql.com.

Using INCLUDE NULLS with UNPIVOT in BigQuery

Comments

Practical SQL

More from this blog

pre_ and post_operations in Dataform

BigQuery Object Tables: A Practical Introduction

BigQuery BigLake Tables Explained: What They Are and When to Use Them

BigQuery Saves Your Query Results — Here's How to Find Them

Parameters in BigQuery

Command Palette

Comments

Practical SQL

More from this blog