Use PIVOT in BIGQUERY with a multi-column ARRAY STRUCT

Question

I have a table in BIGQUERY with three columns, I'm pivoting the first column but I need an ARRAY to be formed with the other two columns:

This is my table:

fecData	total	name
202301	250	first
202302	50	sec
202303	100	th

This is the query with the values:

WITH initTable AS (
  SELECT '202301' AS fecData, 250 AS total, 'first' as name
  UNION ALL
  SELECT '202302' AS fecData, 50 AS total, 'sec' as name
  UNION ALL
  SELECT '202303' AS fecData, 100 AS total, 'th' as name
),
pivotTable AS (
  SELECT *
  FROM initTable
  PIVOT (
    MAX(total)
    FOR fecData IN ('202301', '202302', '202303')
  )
)
SELECT *
FROM pivotTable

This is what I get:

name	202301	202302	202303
first	250	null	null
sec	null	50	null
th	null	null	100

This is what I need:

202301.total	202301.name	202302.total	202302.name	202303.total	202303.name
250	first	50	sec	100	th

I need to convert the fecData values into RECORD REPEATED columns that contain the total and name columns with their respective values. I can't apply an ARRAY(STRUCT on the pivoted columns

Answer 1

You can include multiple aggregation functions in the PIVOT. So, you can try below

WITH initTable AS (
 -- put sample data here
),
pivotTable AS (
  SELECT * FROM initTable
   PIVOT (
     MAX(total) total, ANY_VALUE(name) name
     FOR fecData IN ('202301', '202302', '202303')
   )
)
SELECT * FROM pivotTable

Query results

total_202301	name_202301	total_202302	name_202302	total_202303	name_202303
250	first	50	sec	100	th

---

or you can consider below as well.

pivotTable AS (
  SELECT * FROM initTable
   PIVOT (
     ANY_VALUE(STRUCT(total, name))
     FOR fecData IN ('202301', '202302', '202303')
   )
)

Query results