GBQ Transpose Dynamically without Execute Immediate

Question

Hi All trying to figure out a way to transpose data in columns dynamically. In the data the distinct number of Traits will increase/decrease.

I know I can hardcode the columns like this

SELECT * FROM
  (SELECT * FROM Produce)
  PIVOT(MAX(Trait_Name) FOR Trait IN ('Color', 'Texture', 'Size'))

I can also do it dynamically with Execute Immediate like this

execute immediate (
select '''select * from (select * from `wf-gcp-us-ae-merch-prod.eb_analytics_junk.pr_junk_sku_test`)
  pivot(MAX(Trait_Name) for Trait in ("''' ||  string_agg(Trait, '", "')  || '''"))
'''
);

The issue with Execute Immediate is the results of that must be stored in a table before it can be used again. Is there another method to pivot this data dynamically where it can be put in CTE or SubQuery so the script can continue running?

Source Data

Result Data

Answer 1

Maybe you can use a temp table to store the result of dynamic sql and continue to use it later in the script.

DECLARE produces ARRAY<STRUCT<fruit STRING, trait STRING, trait_name STRING>> DEFAULT [
('Apple', 'Color', 'Red'),
('Apple', 'Texture', 'Smooth'),
('Apple', 'Size', 'Medium'),
('Banana', 'Color', 'Yellow'),
('Banana', 'Texture', 'Rough'),
('Banana', 'Size', 'Large'),
('Grape', 'Color', 'Purple'),
('Grape', 'Texture', 'Soft'),
('Grape', 'Size', 'Small')
];

CREATE TEMP TABLE Produce AS SELECT * FROM UNNEST(produces);

EXECUTE IMMEDIATE FORMAT("""
  CREATE TEMP TABLE pivotted_produce AS
  SELECT * FROM (
    SELECT * FROM Produce
  ) PIVOT(ANY_VALUE(trait_name) FOR trait IN ('%s'))
""", ARRAY_TO_STRING(ARRAY(SELECT DISTINCT trait FROM Produce), "','"));

-- continue to use the result of pivot-ing in following script.
SELECT * FROM pivotted_produce;

output: