How to Use Big Query to Get Single Columns Values from All Rows

2023/06/061 min read
bookmark this
Responsive image

This is not the real scenario I was facing, but let's see you want to get all event_timestamp from a event called page_view for a give day in one column, and the rest of the column in the same row you just keep any page_view the fields.

Below is how you can achieve that at GA4 table.

with test as (
  select * 
  from (
    , row_number() over (partition by event_name) as rn
    FROM `GA4.events_intraday_20230611`
    where event_name = 'page_view'
  where rn = 1
, test_2 as (
  string_agg(safe_cast(event_timestamp as string), '_')
  , event_name
  FROM `GA4.events_intraday_20230611`
  group by event_name
  having event_name = 'page_view'
, test_3 as (
  select test_2.*, test.*
  from test_2
  left join test on test_2.event_name = test.event_name