Have a table in Google Big Query with date, id and store columns where the goal is to keep track how many stores a customer has visited: I wish to create a SQL query which checks for date in first column (A_date). If it has a date (is not null) then keep the date If it is null then check

If null value in a Column A, then how to return the value of column B, and if also B is null, then how to move to the value of C

Have a table in Google Big Query with date, id and store columns where the goal is to keep track how many stores a customer has visited:

|A_date |A_id|Store 1|B_date |B_id  |Store 2|C_date |C_id |Store 3|
--------|----|-------|-------|------|-------|-------|-----|-------|
|21.3.21|John|S1     |21.3.21|John  |S2     |21.3.21|John |S3     |
|21.3.21|Per |S1     |null   |null  |null   |null   |null |null   |
|22.3.21|Tom |S1     |null   |null  |null   |22.3.21|Tom  |S3     |
|null   |null|null   |23.3.21|Sam   |S2     |  null |null |null   |
|null   |null|null   |null   |null  |null   |24.3.21|Rob  |S3     |

I wish to create a SQL query which checks for date in first column (A_date).

If it has a date (is not null) then keep the date
If it is null then check if the next date column (B_date) has a date and return that
If B_date is also null, then check if next date column (C_date) has a date, and return me that date

I wish to end up with a table like this which has a date and id columns along with the store-columns

Date   |ID  |Store 1|Store 2|Store 3|
21.3.21|John|S1     |S2     |S3     |
22.3.21|Tom |S1     |null   |S3     |
23.3.21|Sam |null   |S2     |null   |
24.3.21|Rob |null   |null   |S3     |

Answer

Use coalesce:

select
  coalesce(a_date, b_date, c_date) as date,
  coalesce(a_id, b_id, c_id) as id,
  store_1,
  store_2,
  store_3
from mytable

Advertisement

Answer