动态创建SQLite数据透视表/交叉表的类别 [英] Dynamically create categories for SQLite pivot/crosstab

问题描述

我意识到可以在sqlite中创建一个交叉表，但是有可能在运行时动态确定相关的类别/列，而不是对其进行硬编码吗?

鉴于以下示例，它可能会变得很乏味...

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

我设法通过之前存储的过程，想知道是否有任何等效的方法.

解决方案

不，您不能在SQLite中这样做，因为SQLite中的分析方法缺少此处是相应的票证，但它处于待处理状态. >

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.

解决方案

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.

这篇关于动态创建SQLite数据透视表/交叉表的类别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！