How to use params instead of actual Date using PIVOT in BigQuery?

Question

I am using the below query in my server and I need to make the columns to be dynamic and not String. I tried to use:

    UNNEST(GENERATE_DATE_ARRAY('2024-03-12', '2024-03-25', INTERVAL 1 DAY))

But I got the error:

    Syntax error: Unexpected keyword UNNEST

What is the alternative?

       (
        
        SELECT * FROM
          ( SELECT 
                DATE(Date) AS Date,
                Region,
                Revnue
                
            FROM
          `analy-417.AM.Overview` where 2 is not null
        )
          PIVOT(sum(Revnue) FOR Date IN ('2024-03-25','2024-03-24','2024-03-23','2024-03-22','2024-03-21','2024-03-20','2024-03-19','2024-03-18','2024-03-17','2024-03-16','2024-03-15','2024-03-14','2024-03-13','2024-03-12'))
    
      )

Answer 1

Here's an alternative using left join unnest instead of having to write out all the dates. Made a mock table to how the results in the CTE/with function.

WITH
  mock_data AS (
  SELECT
    '2024-03-12' AS date,
    'NA' AS region,
    999999 AS revenue
  UNION ALL
  SELECT
    '2024-03-12' AS date,
    'NA' AS region,
    200000 AS revenue
  UNION ALL
  SELECT
    '2024-03-13' AS date,
    'NA' AS region,
    0 AS revenue)
SELECT
  date,
  region,
  SUM(revenue) AS revenue_sum
FROM
  mock_data
WHERE
  CAST(date AS date) IN (
  SELECT
    date
  FROM (
    SELECT
      date
    FROM (
      SELECT
        NULL)
    LEFT JOIN
      UNNEST (GENERATE_DATE_ARRAY('2024-03-12', '2024-03-25', INTERVAL 1 DAY)) AS date))
GROUP BY
  date,
  region