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?
Advertisement
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