SQLite3实现动态行转列：列值转为列头无需手动编写查询语句

你当前的固定列写法可以先优化为更高效的CASE WHEN聚合写法，不需要嵌套子查询：

SELECT 
  col1,
  MAX(CASE WHEN col2 = 'col2val1' THEN 「匹配交集展示值」 END) AS col2val1,
  MAX(CASE WHEN col2 = 'col2val2' THEN 「匹配交集展示值」 END) AS col2val2
FROM 原始表名
GROUP BY col1;

SQLite 本身没有内置PIVOT转置函数，也不支持原生的动态SQL执行，所以要实现列头随col2值自动变化的动态转置，按两步操作即可：

第一步：动态生成目标查询语句 #

通过查询col2的所有唯一值，自动拼接出完整的转置查询语句，示例代码如下：

SELECT 'SELECT col1 ' || 
       GROUP_CONCAT(', MAX(CASE WHEN col2 = ''' || col2 || ''' THEN 「匹配交集展示值」 ELSE NULL END) AS `' || col2 || '`') ||
       ' FROM 原始表名 GROUP BY col1;' AS dynamic_sql
FROM (SELECT DISTINCT col2 FROM 原始表名) AS unique_col2;

把代码中的「匹配交集展示值」替换为你实际要在交叉位置显示的内容即可，比如要标记匹配存在就写1，要展示对应其他字段值就填对应字段名。

第二步：执行生成的动态SQL #

两种常用执行场景可选：

• 应用程序调用场景（Python/Java/Go等）
  先执行第一步的查询拿到生成的dynamic_sql字符串，再把这个字符串作为SQL语句发起第二次查询，直接拿到转置后的结果集。以Python的sqlite3库为例：

  import sqlite3
  conn = sqlite3.connect('你的数据库文件路径.db')
  cursor = conn.cursor()
  # 第一步获取动态SQL
  cursor.execute("""
  SELECT 'SELECT col1 ' || 
         GROUP_CONCAT(', MAX(CASE WHEN col2 = ''' || col2 || ''' THEN 1 ELSE NULL END) AS `' || col2 || '`') ||
         ' FROM 原始表名 GROUP BY col1;'
  FROM (SELECT DISTINCT col2 FROM 原始表名);
  """)
  dynamic_sql = cursor.fetchone()[0]
  # 第二步执行动态SQL取结果
  cursor.execute(dynamic_sql)
  # 可通过cursor.description获取动态生成的列头
  headers = [i[0] for i in cursor.description]
  result = cursor.fetchall()
  conn.close()
  

• SQLite命令行工具场景
  开启输出模式把第一步生成的SQL写到临时文件，再读取临时文件执行即可：

  .mode list
  .separator ''
  .output temp_pivot_query.sql
  SELECT 'SELECT col1 ' || 
         GROUP_CONCAT(', MAX(CASE WHEN col2 = ''' || col2 || ''' THEN 1 ELSE NULL END) AS `' || col2 || '`') ||
         ' FROM 原始表名 GROUP BY col1;'
  FROM (SELECT DISTINCT col2 FROM 原始表名);
  .output stdout
  .read temp_pivot_query.sql
  

补充说明 #

每次col2有新增、删除值的时候，重新走一遍上述两步流程，就能自动生成对应列头的转置结果，不需要手动修改查询逻辑。如果需要聚合计算匹配交集的值，把CASE WHEN外层的MAX函数替换为SUM、COUNT等对应聚合函数即可。

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