0

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

codninja0908
  • 497
  • 8
  • 29

1 Answers1

2

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
'''
Mikhail Berlyant
  • 165,386
  • 8
  • 154
  • 230
  • It worked .. Thanks ..In standard Sql instead of + for concatenation we use || . Could you point me to some link which defines that.? – codninja0908 Jun 24 '20 at 17:45
  • 1
    sure. see [Operators in Standard SQL](https://cloud.google.com/bigquery/docs/reference/standard-sql/operators) – Mikhail Berlyant Jun 24 '20 at 17:54
  • Can we pass column names as parameters in order to use the query for multiple purposes wherein I can just replace Aggregate key, pivot key with some other column? – codninja0908 Jun 24 '20 at 18:36
  • generic answer - yes. but if you need help with coding - please post new question with all relevant details and hopefully we will be able to help :o) – Mikhail Berlyant Jun 24 '20 at 18:38
  • Started new thread : [link](https://stackoverflow.com/questions/62562149/parametrized-table-name-and-column-name-in-standard-sql-bigquery) – codninja0908 Jun 24 '20 at 18:53
  • check the answer in https://stackoverflow.com/q/62517621/5221944 – Mikhail Berlyant Jun 24 '20 at 20:23