Basic Join Question to Join A Table to Itself based on a condition in Big Query-CodePudding

I want to join a table to itself to apply values of a column to all the values in another column. LOGIC IS- WHEN WORKFLOW = "PICK" then TAKE THE DAY and USE THAT DAY FOR THE WHOLE ID.

TABLE

ID | Workflow   | Day        |
1  | PICK       | 2022.11.01 |
1  | tew        | 2022.11.02 | 
1  | wte        | 2022.11.03 |
1  |            | 2022.11.04 |
1  |            | 2022.11.05 | 
2  | PICK       | 2022.11.06 |

The answer would be

ID | Workflow   | Day        |
1  | PICK       | 2022.11.01 |
1  | tew        | 2022.11.01 | 
1  | wte        | 2022.11.01 |
1  |            | 2022.11.01 |
1  |            | 2022.11.01 | 
2  | PICK       | 2022.11.06 |

The Workflow = 'PICK' date was applied to the whole ID as the date.

CodePudding user response：

You might consider a window function instead of self join.

WITH sample_data AS (
  SELECT 1 ID, 'PICK' Workflow, '2022.11.01' Day UNION ALL
  SELECT 1 ID, 'tew' Workflow, '2022.11.02' Day UNION ALL
  SELECT 1 ID, 'wte' Workflow, '2022.11.03' Day UNION ALL
  SELECT 1 ID, null Workflow, '2022.11.04' Day UNION ALL
  SELECT 1 ID, null Workflow, '2022.11.05' Day UNION ALL
  SELECT 2 ID, 'PICK' Workflow, '2022.11.06' Day
)
SELECT ID, Workflow,
       STRING_AGG(IF(Workflow = 'PICK', Day, NULL)) OVER (PARTITION BY ID) AS Day
  FROM sample_data;

Query results

If you need to use self join, you can consider below query as well having same output above.

SELECT t.* EXCEPT(Day), s.Day
  FROM sample_data t 
  LEFT JOIN sample_data s ON t.ID = s.ID AND s.Workflow = 'PICK';