BigQuery : transform row into columns dynamically

Question

In BigQuery

Table issue_fields :

issue_id	field_name	field_value
101	city	NY
101	size	10 m2
103	postalcode	1020
103	city	SF

Table issues :

| issue_id | created_date

I want, in BigQuery, to create a table which is the issues table with n new columns, every column is a field_name, for all field_name in the table issue_fields.

Desired output :

issue_id	created_date	city	size	postalcode
101	2023	NY	10 m2
103	2022	SF		1020

Tried with ChatGPT without success

Answer 1

You can get the following output (excluding the created_date field) using the below code.

SELECT
  issue_id,
  MAX(IF(field_name = 'city', field_value, NULL)) AS city,
  MAX(IF(field_name = 'size', field_value, NULL)) AS size,
  MAX(IF(field_name = 'postalcode', field_value, NULL)) AS postalcode
FROM
  your_table
GROUP BY
  issue_id

This will use if statements to extract the key value pairs, returning the desired output table.

Answer 2

You can use the conditional aggregation :

select issue_id,
       max(created_date) as created_date,
       max(case when field_name = 'city' then field_value end ) as city,
       max(case when field_name = 'size' then field_value end ) as size,
       max(case when field_name = 'postalcode' then field_value end ) as postalcode
from mytable
group by issue_id

Answer 3

you need a script to accomplish this

    BEGIN
    DECLARE dynamic_fields STRING ;
    -- This execute block selects all field_name
    EXECUTE IMMEDIATE FORMAT("""
      WITH
        fields AS (
          SELECT 101 AS issue_id, "city" AS field_name, "NY" as field_value
          UNION ALL
          SELECT 101 AS issue_id, "size" AS field_name, "10m2" as field_value
          UNION ALL
          SELECT 103 AS issue_id, "postalcode" AS field_name, "1020" as field_value
          UNION ALL
          SELECT 103 AS issue_id, "county" AS field_name, "SF" as field_value
      ) SELECT 

---- The string_agg the list of all possible values you want to select ----

string_agg( DISTINCT CONCAT("'",fields.field_name,"'") )
      FROM fields
    """) INTO dynamic_fields;
    SELECT dynamic_fields;
    
    EXECUTE IMMEDIATE FORMAT("""
         WITH
         fields_set AS (
           SELECT 101 AS issue_id, "city" AS field_name, "NY" as field_value
           UNION ALL
           SELECT 101 AS issue_id, "size" AS field_name, "10m2" as field_value
           UNION ALL
           SELECT 103 AS issue_id, "postalcode" AS field_name, "1020" as field_value
           UNION ALL
           SELECT 103 AS issue_id, "county" AS field_name, "SF" as field_value
       ) SELECT *
       FROM fields_set

-- This pivot section selects into the distinct list in previous block

       PIVOT(COUNT(fields_set.issue_id) FOR fields_set.field_name IN (%s))""",dynamic_fields);
    END;