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:
x
|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 |
Advertisement
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