Loop Concat in BigQuery

I have data like this :

Time          Route
11:03:01      home
11:03:04      category
11:03:10      product
11:03:21      cart

I want to create this :

Time          Route        Journey
11:03:01      home         home
11:03:04      category     home, category
11:03:10      product      home, category, product
11:03:21      cart         home, category, product, cart

How can I do this in BigQuery?

Answer

Below is for BigQuery Standard SQL

#standardSQL
SELECT *, STRING_AGG(route) OVER(ORDER BY time) journey
FROM `project.dataset.table`

You can test, play with above using sample data from your question as in below example

#standardSQL
WITH `project.dataset.table` AS (
  SELECT TIME '11:03:01' time, 'home' route UNION ALL
  SELECT '11:03:04', 'category' UNION ALL
  SELECT '11:03:10', 'product' UNION ALL
  SELECT '11:03:21', 'cart' 
)
SELECT *, STRING_AGG(route) OVER(ORDER BY time) journey
FROM `project.dataset.table`
-- ORDER BY time   

with result

Row time        route       journey  
1   11:03:01    home        home     
2   11:03:04    category    home,category    
3   11:03:10    product     home,category,product    
4   11:03:21    cart        home,category,product,cart