不使用扩展如何在SQLite中实现关联表行的动态转置

SQLite本身不支持在SQL层直接执行动态拼接的语句，因此无法像MySQL一样单步完成动态pivot，但可以通过两步纯SQL操作实现，无需任何扩展，可直接在在线SQLite IDE中运行：

• 第一步：执行以下SQL生成最终的转置查询语句

SELECT 'SELECT m.idMainTable, m.MoreStuff, ' ||
       GROUP_CONCAT(DISTINCT 'MAX(CASE WHEN s.keyColumn = ''' || keyColumn || ''' THEN s.value END) AS ' || quote(keyColumn)) ||
       ' FROM MainTable m LEFT JOIN SecondaryTable s ON m.idMainTable = s.idMainTable GROUP BY m.idMainTable, m.MoreStuff' AS pivot_query
FROM SecondaryTable;

• 第二步：复制第一步查询结果中输出的pivot_query字段内容，单独执行该SQL语句，即可得到你期望的行转置结果。

方案说明 #

• 选用LEFT JOIN关联两张表，保证主表中没有关联键值对的记录也会出现在结果集中，若不需要该特性可替换为INNER JOIN
• 使用quote()函数包裹生成的列名，可兼容key包含空格、引号等特殊字符的场景，避免出现SQL语法错误
• 未匹配到的key对应的单元格默认返回NULL，若需要返回空字符串，可将THEN s.value END修改为THEN s.value ELSE '''' END

以你提供的示例数据为例，第一步生成的最终查询语句如下，执行后完全匹配预期输出：

SELECT m.idMainTable, m.MoreStuff, 
MAX(CASE WHEN s.keyColumn = 'Key1' THEN s.value END) AS 'Key1',
MAX(CASE WHEN s.keyColumn = 'Key5' THEN s.value END) AS 'Key5',
MAX(CASE WHEN s.keyColumn = 'Key7' THEN s.value END) AS 'Key7',
MAX(CASE WHEN s.keyColumn = 'Key8' THEN s.value END) AS 'Key8',
MAX(CASE WHEN s.keyColumn = 'Key4' THEN s.value END) AS 'Key4',
MAX(CASE WHEN s.keyColumn = 'Key25' THEN s.value END) AS 'Key25',
MAX(CASE WHEN s.keyColumn = 'Key2' THEN s.value END) AS 'Key2',
MAX(CASE WHEN s.keyColumn = 'Key6' THEN s.value END) AS 'Key6'
FROM MainTable m LEFT JOIN SecondaryTable s ON m.idMainTable = s.idMainTable GROUP BY m.idMainTable, m.MoreStuff;

内容的提问来源于stack exchange，提问作者Alberto Casas Ortiz