0

My reference is Transpose rows into columns in BigQuery (Pivot implementation)

I tried to implement the recommendation in standard big query by replacing group_concat_unquoted by string_agg but I don't succeed in using the correct syntax and structure.

WITH
input AS (
SELECT
    user_pseudo_id AS user_id,
    event_timestamp,
    p.key AS key,
    p.value.string_value AS value
  FROM
    `myproject.analytics_xxxx.events_*`,
    UNNEST(event_params) AS p
  WHERE
    AND event_name = 'event_type_to_pivot' )
SELECT
  'select user_id, ' + STRING_AGG(
  'MAX(IF(key = "'+key+'", value, NULL)) as ['+key+']'
  ) + 'FROM input group by user_id order by user_id'
FROM (
  SELECT
    key
  FROM
    input
  GROUP BY
    key
  ORDER BY
    key )

I get consistently an error : No matching signature for operator + for argument types: STRING, STRING. Supported signatures: INT64 + INT64; FLOAT64 + FLOAT64; NUMERIC + NUMERIC at [19:3] for line:

STRING_AGG(
      'MAX(IF(key = "'+key+'", value, NULL)) as ['+key+']'
      )

After a lot of tries, I still don't get how to use properly string_agg to transpose rows into columns.

Thanks for your guidance,

MrOhNoImissedThatPoint
  • 21
  • 6

1 Answers1

0

In #standardSQL use CONCAT() instead of + to compose strings:

'MAX(IF(key = "'+key+'", value, NULL)) as ['+key+']'

->

CONCAT('MAX(IF(key = "',key,'", value, NULL)) as [',key,']')
Felipe Hoffa
  • 54,922
  • 16
  • 151
  • 325