Dynamically create categories for SQLite pivot/crosstab

Question

I realise it is possible to create a crosstab within sqlite, but is it possible to dynamically determine the relevant categories/columns at runtime rather than hardcoding them?

Given the following example, it can get rather tedious ...

SELECT 
shop_id,
sum(CASE WHEN product = 'Fiesta' THEN units END) as Fiesta,
sum(CASE WHEN product = 'Focus' THEN units END) as Focus,
sum(CASE WHEN product = 'Puma' THEN units END) as Puma,
sum(units) AS total

FROM sales
GROUP BY shop_id

I managed to do this in SQLServer in a stored proceedure before and wondered if there was anything equivalent.

Answer 1

No, you can't do that in SQLite, since analytic means in SQLite lack many usefull features available in heavier RDBMSes. Here is the corresponding ticket, but it is in the pending status.