I have a question regarding data transport in BQ (or actually export and do it in Excel). I am trying to get this result (Sorry I am new and not sure how to separate 2 columns, variant1 and variant2 should be 2 columns) :

My current query will give this output:

SELECT DISTINCT
  case when (hits.ecommerceAction.action_type = '3') then date end date, [enter image description here][1]
  clientId AS client_id,
  page.pagepath as pagepath,
  product.productVariant as variant,
FROM
  `xxxx.ga_sessions_`,
  UNNEST(hits) AS hits, unnest(hits.product) as product 

Is there anyway that I can use to achieve the transpose step? My current output is more like a master data, all the product related information is under one column. Appreciate if you can share any thoughts!

CodePudding user response：

Consider below approach

select * from (
  select ClientID, Variant, Pagepath,
    max(Date) over win Date,
    row_number() over (win order by Date) pos
  from your_current_output
  window win as (partition by ClientID)
)
pivot (any_value(Variant) as Variant, any_value(Pagepath) as Pagepath for pos in (1,2,3))     

if to apply to sample in your question

with your_current_output as (
  select '12/1' Date, 123 ClientID, 'abc' Variant, 'fis.com' Pagepath union all
  select '12/2', 123, 'efg', 'fere.com'
)    

output is