Bigquery Standard Sql equivalent for GROUP_CONCAT_UNQUOTED from Legacy Sql

Question

I am currently using Bigquery for dynamic generation of query using "GROUP_CONCAT_UNQUOTED" function for concatenation of multiple strings to one.

Example:

SELECT 'SELECT ID, AID, ' + 
   GROUP_CONCAT_UNQUOTED(
      'MAX(IF(KEY = "' + KEY + '", VALUE, NULL)) as [' + KEY + ']'
   ) 
   + ' FROM [project:dataset.tbl] GROUP BY 1,2 ORDER BY 1,2'
FROM (
  SELECT KEY 
  FROM [project:dataset.tbl]
  GROUP BY KEY
  ORDER BY KEY
)

Above query produces yet another query like below:

SELECT 
    ID, 
    AID, 
    MAX(IF(KEY = "key1", VALUE, NULL)) as [key1],
    MAX(IF(KEY = "key2", VALUE, NULL)) as [key2],
    MAX(IF(KEY = "key3", VALUE, NULL)) as [key3] 
FROM [project:dataset.tbl]
GROUP BY 1,2 
ORDER BY 1,2

I would like to do the same using Standard SQL instead of Legacy SQL.

Google docs mentions STRING_AGG() as an alternative but I am not getting the desired output

Mikhail Berlyant Mikhail Berlyant · Accepted Answer · 2020-06-24T14:51:34

Below is for BigQuery Standard SQL

#standardSQL
SELECT '''
SELECT ID, AID, ''' || (
  SELECT STRING_AGG(DISTINCT "MAX(IF(key = '" || key || "', value, NULL)) AS " || key)
  FROM `project.dataset.table`
) || '''  
FROM `project.dataset.table`
GROUP BY 1,2
ORDER BY 1,2
'''

Also yo can use EXECUTE IMMEDIATE to actually execute just built query in one shot

EXECUTE IMMEDIATE '''
SELECT ID, AID, ''' || (
  SELECT STRING_AGG(DISTINCT "MAX(IF(key = '" || key || "', value, NULL)) AS " || key)
  FROM `project.dataset.table`
) || '''  
FROM `project.dataset.table`
GROUP BY 1,2
ORDER BY 1,2
'''

Bigquery Standard Sql equivalent for GROUP_CONCAT_UNQUOTED from Legacy Sql

1 Answers