Pivot statement unsupported?

[ericemc3]

CREATE TABLE cities (
    country VARCHAR, name VARCHAR, year INTEGER, population INTEGER
);
INSERT INTO cities VALUES
    ('NL', 'Amsterdam', 2000, 1005),
    ('NL', 'Amsterdam', 2010, 1065),
    ('NL', 'Amsterdam', 2020, 1158),
    ('US', 'Seattle', 2000, 564),
    ('US', 'Seattle', 2010, 608),
    ('US', 'Seattle', 2020, 738),
    ('US', 'New York City', 2000, 8015),
    ('US', 'New York City', 2010, 8175),
    ('US', 'New York City', 2020, 8772);

Second cell:

PIVOT cities
ON year
USING first(population);

=> Invalid Input Error: Cannot prepare multiple statements at once!

[jraymakers]

Ah, yes, this is a known problem in the interaction between now PIVOT is implemented and the PendingQuery C++ API call we use to run queries. PIVOT gets expanded into multiple statements, which PendingQuery can't handle.

We can probably address this by using Query, at least in this case. We'll have to think a bit about the best way to do that, without negatively affecting other queries (since PendingQuery provides some advantages, such as prompt cancellation).

[pugpapa]

Is there a workaround currently?

[jraymakers]

There's no workaround that I'm aware of.