0

I need to pass column name and table name as parameters to the query at run time.

Example

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

In the same query above would like to replace hardcoded AID, key with parameterised value and the same for table name as well.

#standardSQL
DECLARE 
SELECT '''
SELECT <parameter col>, ''' || (
  SELECT STRING_AGG(DISTINCT "MAX(IF(<parameter col>= '" || <parameter col>|| "', value, NULL)) AS " || <parameter col>)
  FROM `project.dataset.table`
) || '''  
FROM `project.dataset.table`
GROUP BY 1
ORDER BY 1
'''
codninja0908
  • 497
  • 8
  • 29
  • i can understand passing list of params instead of AID, i can also understand reason in passing table name. But what is the reason for passing keys, vs. extracting them from data itself as it was in previous question. So, to clarify, can you please rather present your use case with sample of input data and expected output – Mikhail Berlyant Jun 24 '20 at 19:01
  • The use case is for pivoting... [link]https://stackoverflow.com/questions/62517621/convert-long-table-to-wide-table-in-bigquery – codninja0908 Jun 24 '20 at 19:34
  • i just answered referenced question - https://stackoverflow.com/a/62563473/5221944 – Mikhail Berlyant Jun 24 '20 at 20:24
  • That was the use case I mentioned but i am still looking to have a parameterised columns – codninja0908 Jun 25 '20 at 09:38

0 Answers0