SQL Pro tips : GCP BigQuery SQL CROSS JOIN with UNPIVOT UNNEST
Tags: gcp, sql, database, bigdata
Published: 2023-10-06 15:10:53

## Introduction
TL;DR : Why to use `UNPIVOT` over `UNION ALL` ? Performance, `UNION ALL` scans all the data for each subquery, with `UNPIVOT` the scan happens only once.
We shall perform now the same operation with [BigQuery](https://console.cloud.google.com/bigquery)
Feel free to check the [Oracle XE CROSS JOIN UNPIVOT tips](https://dev.to/hexfloor/sql-pro-tips-oracle-sql-cross-join-with-unpivot-57ff) or a [post on the internet](https://towardsdatascience.com/how-to-unpivot-multiple-columns-into-tidy-pairs-with-sql-and-bigquery-d9d0e74ce675).
I'm writing this guide to provide a simple step-by-step manual easy to understand.

## UNPIVOT
Let's start straight away with something trivial : 

```
select 'SUNDAY' as day0, 'MONDAY' as day1;
```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/t4ur0n37f2xmrttac3tc.png)
Similar example with array : 

```
select array['SUNDAY','MONDAY'] as day;
```
Look carefully, in fact there is only 1 row :

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rind6bn2yn3lf9p53nrp.png)

And once you have performed [UNNEST] (https://cloud.google.com/bigquery/docs/arrays) : 

```
select * from unnest(array['SUNDAY','MONDAY']);
```

you have 2 rows : 

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bjeke9w60h6v18yqrg2m.png)
As explained in the [UNNEST documentation](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#unnest_operator)
the operator takes an `array` as an input and returns a table with a single row for each element in the `array`.

What if we have to `UNPIVOT` multiple values ?
The comma in between the `UNNEST` in the query below stands for the `CROSS JOIN` : 

```
select * from unnest(array['SUNDAY','MONDAY']) as day, unnest(array[0,1]) as day_order;
```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1ahutue1rg3o8j53u9k4.png)

This is now what we wanted to achieve, hence let's try to use [UNPIVOT](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#unpivot_operator)

```
with data as (select 'SUNDAY' as day0, 0 as day_order0, 'MONDAY' as day1, 1 as day_order1)
select * from data
unpivot(
  (day, day_order) for day_column in (
    (day0, day_order0) as 'day0',
    (day1, day_order1) as 'day1'
  ) 
);
```


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/oijjwpo7dmztxcuwc9og.png)

Great ! Now we know how to use `UNNEST` and `UNPIVOT`, let's dive into `CROSS JOIN`.

## CROSS JOIN
In a nutshell [CROSS JOIN](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#cross_join)
operation is simple = it's just a Cartesian product of the two tables, take all the possible combinations from the values on the left with the values on the right.
Few examples : 
 

```
with 
  week_data as (select '2024W01' as week_ref),
  day_data as (select 'SUNDAY' as day_ref)
select * from week_data cross join day_data;
```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/d81f7uv9eqfc6gkfvr7v.png)
Now let's have some `UNION ALL`

```
with 
  week_data as (select '2024W01' as week_ref),
  day_data as (
    select 'SUNDAY' as day_ref
    union all 
    select 'MONDAY' as day_ref)
select * from week_data cross join day_data;
```


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5fwfom6m5kscripxenlp.png)

a bit of `array` : 

```
with 
  week_data as (select '2024W01' as week_ref),
  day_data as (
    select array['SUNDAY', 'MONDAY'] as day_ref)
select * from week_data cross join day_data;
```
look carefully, there is only 1 row in the result : 

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6rvpusj13tu2ttlzkhiy.png)

let's have 2 rows with `UNNEST` : 

```
with 
  week_data as (select '2024W01' as week_ref),
  day_data as (
    select * from unnest(array['SUNDAY', 'MONDAY']) as day_ref)
select * from week_data cross join day_data;
```


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/p0v8uxfklb5ahq3daxex.png)

and finally the full power of `CROSS JOIN` : 

```
with 
  week_data as (select * from unnest(array['2024W01', '2024W02']) as week_ref),
  day_data as (
    select * from unnest(array['SUNDAY', 'MONDAY']) as day_ref)
select * from week_data cross join day_data;
```


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/s6shqge7fz3d2jocxr8o.png)

We are ready now to combine the `CROSS JOIN` with `UNPIVOT` or `UNNEST`.

## CROSS JOIN UNPIVOT
Let's combine both `CROSS JOIN` and `UNPIVOT`, first I will generate some data that we would like to slice and to stack the chunks : 

```
with 
  in_data as (select '2024W01' as week_ref, 'SUNDAY' as day0, 0 as day_order0, 'MONDAY' as day1, 1 as day_order1)
select * from in_data;
```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/z78usyl2gake11yckfn6.png)

And let's have the desired result : 

```
with 
  in_data as (select '2024W01' as week_ref, 'SUNDAY' as day0, 0 as day_order0, 'MONDAY' as day1, 1 as day_order1)
select week_ref, day, day_order from in_data
unpivot(
  (day, day_order) for day_column in (
    (day0, day_order0) as 'day0',
    (day1, day_order1) as 'day1'
  ) 
);
```


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/flk7gu8lrbzyl1uwl9db.png)

Thanks to the examples above you know that what happens here is `CROSS JOIN` of the `week_ref` on the left with the result of `UNPIVOT` on the right.

If you have only one column you can take a shortcut with `UNNEST` :

```
with 
  in_data as (select '2024W01' as week_ref, 'SUNDAY' as day0, 0 as day_order0, 'MONDAY' as day1, 1 as day_order1)
select week_ref, day from in_data
cross join unnest(array[day0, day1]) as day;
```


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ub30qu1uckc64uuod6jy.png)

Enjoy!