SQL Pattern to merge time series data into a single 'date' column

Question

I have some housing data imported into BigQuery and it looks like this -

zip_code | date_1 | date_2 | date_3 ...

12345 | 01/01/00 | 01/08/00 | 01/15/00 ....

I would like to transform it into something like this in BQ:

zip_code | date

12345 | 01/01/00 12345 | 01/08/00 12345 | 01/15/00 ....

Could someone please help with a SQL pattern for simply do this transformation?

Answer 1

Try using the unpivot operator as follows:

with sample_data as (
    select 12345 as zip_code, '01/01/00' as date_1, '01/08/00' as date_2, '01/15/00' as date_3
)

select up.zip_code, up.date
from sample_data
unpivot(date for col_name in (date_1, date_2, date_3)) up

This will produce data like:

For more information on the unpivot operator check the documentation here: https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#unpivot_operator