SQLite动态透视查询：按Buildings统计各未知Version的平均MS

你要找的这类把行维度值转换为列名、行列交叉位置填充聚合值的操作，正式名称是透视（Pivot），也常被称为交叉表（Crosstab）、行转列，之前检索不到方案基本是因为没匹配到这个关键词。

SQLite 没有内置原生PIVOT语法，且标准SQL本身要求查询的列结构必须在语句执行前就确定，无法直接在单条SQL里自动生成数量、名称都未知的动态列，要实现需求可以用以下两类方案：

• 方案一：两步法动态拼接SQL
  1. 先执行查询拿到所有去重的Version值：

     SELECT DISTINCT version FROM loadtimes ORDER BY version;
     

  2. 基于拿到的Version列表，拼接生成条件聚合的查询语句，每个Version对应一个条件判断分支，语句结构如下：

     SELECT
       buildings,
       -- 以下分支根据第一步查到的Version值循环拼接即可
       AVG(CASE WHEN version = 'v1.0' THEN ms END) AS "v1.0",
       AVG(CASE WHEN version = 'v1.1' THEN ms END) AS "v1.1",
       AVG(CASE WHEN version = 'v2.0' THEN ms END) AS "v2.0"
     FROM loadtimes
     GROUP BY buildings
     ORDER BY buildings;
     

     如果是在sqlite3命令行、封装了SQLite执行能力的程序环境中，可以直接通过脚本自动完成拼接，不需要手动复制版本号，注意拼接时要做好版本号特殊字符的转义，避免语法错误。
• 方案二：程序侧转换（适配绘图场景，更推荐）
  你最终的结果是用于数据绘图，通常是在Python、R等数据分析/绘图代码中连接SQLite取数，这种场景完全不需要在SQL层强行转宽表：
  1. 直接执行你已经写好的长表查询即可，建议给聚合值加个别名方便后续取数：

     SELECT AVG(ms) AS avg_ms, version, buildings FROM loadtimes GROUP BY buildings, version;
     

  2. 取到长表结果后，用你所用语言的现成透视方法转宽表即可，比如Python pandas的pivot_table、R tidyr的pivot_wider都是专门做这个转换的，代码量少、容错性高，后续Version值增减也不需要改SQL逻辑。

不建议写死列名写静态透视语句，只要Version列表有新增、修改，静态语句就会出现列缺失，无法适配动态场景。

内容的提问来源于stack exchange，提问作者Nefariis