Pivot data in Dataform

config {
  type: "operations",
  hasOutput: true // Necessary for dependency graphs.
}

/*
  PARAMETERS TO CUSTOMIZE:
  - source_schema: The schema of your source table.
  - source_table: The table you're pivoting.
  - dynamic_column: The column to pivot on.
  - dimension_1, dimension_2: Columns you want to group by.
  - pivot_metric: The metric column you're aggregating.
  - aggregation_function: The aggregation function (e.g., AVG, SUM).
  - order_by_column: The column to order your results by.
*/

DECLARE pivot_columns STRING;

-- Dynamic SQL for pivoting
DECLARE dynamic_sql STRING;
SET dynamic_sql = FORMAT("""
  CREATE OR REPLACE TABLE ${self()} FROM (
    SELECT 
      dimension_1,
      dimension_2,
      pivot_metric
    FROM ${ref({name:"source_table", schema:"source_schema"})}
  ) PIVOT (
    %s(pivot_metric) AS pivot_metric
    FOR dynamic_column IN (%s)
  ) ORDER BY %s ASC
""", 
  "aggregation_function", -- Placeholder for the aggregation function, replace as needed
  pivot_columns,
  "order_by_column" -- Placeholder for ordering, replace with dimension_1, dimension_2 as required
);

-- Generate the dynamic pivot columns
SET pivot_columns = (
  SELECT 
    STRING_AGG(DISTINCT FORMAT('"%s"', dynamic_column), ', ')
  FROM ${ref({name:"source_table", schema:"source_schema"})}
);

-- Execute the dynamic SQL
EXECUTE IMMEDIATE dynamic_sql;