Using INCLUDE NULLS with UNPIVOT in BigQuery

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.