just trying my luck here as I’ve thought I’d searched enough for an answer but is a bit out of luck.

I have this query,

SELECT child_name,
    SUM(CASE WHEN date_string = '2025' THEN _value END) AS "X.2025",
    SUM(CASE WHEN date_string = '2026' THEN _value END) AS "X.2026",
    SUM(CASE WHEN date_string = '2027' THEN _value END) AS "X.2027",
    SUM(CASE WHEN date_string = '2028' THEN _value END) AS "X.2028",
    SUM(CASE WHEN date_string = '2029' THEN _value END) AS "X.2029",
    SUM(CASE WHEN date_string = '2030' THEN _value END) AS "X.2030",
    SUM(CASE WHEN date_string = '2031' THEN _value END) AS "X.2031",
    SUM(CASE WHEN date_string = '2032' THEN _value END) AS "X.2032",
    SUM(CASE WHEN date_string = '2033' THEN _value END) AS "X.2033"
FROM prod."tbl.SystemRegions"
WHERE ("model_name" = model_name)
AND (property_name = 'Load')
GROUP BY child_name
ORDER BY child_name;`

is there a way to make the pivot dynamic, because different “model_name”s will have different start and end date_string?

What I’ve tried is this on a test table,

CREATE TABLE test_stuff_here."ProductSales" (
    Productname varchar(50),
    year_value text,
    Sales int
);

INSERT INTO test_stuff_here."ProductSales" VALUES
    ('A','2017',100),
    ('A','2018',150),
    ('A','2019',300),
    ('A','2020',500),
    ('A','2021',450),
    ('A','2022',675),
    ('B','2018',900),
    ('B','2019',1120),
    ('B','2020',750),
    ('B','2021',1500),
    ('B','2022',1980);

    CREATE OR REPLACE FUNCTION test_stuff_here.pivot_dynamic(IN table_name text) RETURNS void AS $$
    DECLARE
    col_names TEXT;
    dynamic_sql TEXT;
    BEGIN
    SELECT STRING_AGG(DISTINCT year_value, ', ') INTO col_names
    FROM test_stuff_here."ProductSales";

        dynamic_sql := format('
        SELECT productname, ' || col_names || '
        FROM prod.crosstab(
             SELECT productname, year_value, sales FROM test_stuff_here.%I,

             ) AS ct (productname varchar, ' || col_names || ' text)', table_name
         );

     EXECUTE dynamic_sql;

END;
$$ LANGUAGE plpgsql;

And I’m getting this error

ERROR:  syntax error at or near "SELECT"
LINE 4: SELECT productname, year_value, sales FROM test_...
        ^
QUERY:  
SELECT productname, 2017, 2018, 2019, 2020, 2021, 2022
FROM prod.crosstab(
SELECT productname, year_value, sales FROM test_stuff_here."ProductSales",

    ) AS ct (productname varchar, 2017, 2018, 2019, 2020, 2021, 2022 text)

CONTEXT:  PL/pgSQL function test_stuff_here.pivot_dynamic(text) line 17 at EXECUTE

SQL state: 42601