SQLite中如何将两列分组统计结果转为行列转置格式？

假设我有包含name（姓名）和ice cream purchases（冰淇淋购买记录）两列的数据：

Joe  | Chocolate
Mary | Vanilla
Beth | Rocky Road
Fred | Vanilla
Mary | Rocky Road
Joe  | Vanilla
Joe  | Chocolate
etc...

我需要按这两列分组统计计数。我知道如何得到包含name、flavor、count三列的结果，但希望将姓名作为行，冰淇淋口味作为列，输出如下格式：

+ Vanilla | Chocolate | Rocky Road
Joe  |    1    |     2     |      0
Mary |    1    |     0     |      1
Beth |    0    |     0     |      1
Fred |    1    |     0     |      0

能否仅通过SQL查询实现该需求？数据库为SQLite。

SQLite本身没有内置的PIVOT函数，但可以通过两种方式实现你要的行列转换效果：

1. 已知所有冰淇淋口味的情况 #

如果提前知道所有可能的口味（比如示例里的Vanilla、Chocolate、Rocky Road），可以用CASE WHEN结合聚合函数COUNT()来手动生成列：

SELECT
    name,
    COUNT(CASE WHEN "ice cream purchases" = 'Vanilla' THEN 1 END) AS Vanilla,
    COUNT(CASE WHEN "ice cream purchases" = 'Chocolate' THEN 1 END) AS Chocolate,
    COUNT(CASE WHEN "ice cream purchases" = 'Rocky Road' THEN 1 END) AS "Rocky Road"
FROM
    your_table_name
GROUP BY
    name;

• 原理：CASE WHEN会在匹配到对应口味时返回1，否则返回NULL；COUNT()会忽略NULL值，从而统计出每个用户对应口味的购买次数。
• 没有匹配的情况COUNT()会自动返回0，无需额外处理。

2. 口味未知或动态变化的情况 #

如果冰淇淋口味是动态新增的，没法提前写死在SQL里，可以用SQLite的字符串函数生成动态SQL，再执行：

步骤1：生成动态列的SQL片段 #

先查询所有不同的口味，拼接成COUNT(...) AS 口味的格式：

SELECT GROUP_CONCAT(
    'COUNT(CASE WHEN "ice cream purchases" = ''' || flavor || ''' THEN 1 END) AS ''' || flavor || ''''
) AS pivot_columns
FROM (SELECT DISTINCT "ice cream purchases" AS flavor FROM your_table_name);

步骤2：拼接完整的SQL并执行 #

把上面得到的pivot_columns结果插入到主查询中，最终的完整SQL类似：

SELECT
    name,
    -- 这里替换成步骤1得到的pivot_columns内容
    COUNT(CASE WHEN "ice cream purchases" = 'Vanilla' THEN 1 END) AS 'Vanilla',
    COUNT(CASE WHEN "ice cream purchases" = 'Chocolate' THEN 1 END) AS 'Chocolate',
    COUNT(CASE WHEN "ice cream purchases" = 'Rocky Road' THEN 1 END) AS 'Rocky Road'
FROM
    your_table_name
GROUP BY
    name;

你需要通过编程语言（比如Python、Java）或者SQLite的命令行工具来动态拼接并执行这段SQL，因为SQLite本身不支持直接执行动态生成的SQL语句。

内容的提问来源于stack exchange，提问作者T. Reed