Dynamically create categories for SQLite pivot/crosstab
- by alj
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.
