SQLite能否实现数据透视？如何转换CSV多行数据为宽表？

SQLite对数据透视的支持 #

SQLite没有原生的PIVOT关键字，但可以通过聚合函数+条件判断的方式手动实现行转列（数据透视），完全不需要额外扩展，适配Mac/Linux系统自带的sqlite3环境。

具体实现方案 #

已知最大IP列数为4，我们可以通过为每个host分组标记IP序号，再用条件聚合提取对应位置的IP值，完成从长表到宽表的转换。

完整Shell脚本代码 #

将以下代码保存为pivot_csv.sh（或直接在终端执行），确保输入文件为IN.csv：

#!/bin/bash
sqlite3 :memory: <<EOF
.mode csv
.import IN.csv temp_table
-- 为每个host下的IP生成顺序序号
WITH numbered_ips AS (
    SELECT 
        host,
        ip,
        ROW_NUMBER() OVER (PARTITION BY host ORDER BY ip) AS ip_num
    FROM temp_table
)
-- 行转列生成宽表
SELECT 
    host,
    MAX(CASE WHEN ip_num=1 THEN ip END) AS ip1,
    MAX(CASE WHEN ip_num=2 THEN ip END) AS ip2,
    MAX(CASE WHEN ip_num=3 THEN ip END) AS ip3,
    MAX(CASE WHEN ip_num=4 THEN ip END) AS ip4
FROM numbered_ips
GROUP BY host
ORDER BY host;
EOF

代码说明 #

1. 内存表加载：使用:memory:创建内存数据库，通过.import IN.csv temp_table将CSV数据导入临时表temp_table。
2. 标记IP序号：利用ROW_NUMBER() OVER (PARTITION BY host ORDER BY ip)为每个host下的IP按顺序标记序号，确保每个IP对应唯一的列位置。
3. 条件聚合转宽表：通过MAX(CASE ...)的方式，按序号提取对应位置的IP值；无对应IP的位置会返回NULL，在CSV输出中表现为空单元格。

输出结果 #

执行脚本后会输出符合要求的宽表：

host,ip1,ip2,ip3,ip4
a.com,ns1.a.com,ns2.a.com,,
b.com,ns1.b.com,ns2.b.com,ns3.b.com,
c.com,ns1.c.com,,,

如果只需要实际用到的列（比如最大3列），只需删除SELECT语句中的ip4相关代码，即可得到：

host,ip1,ip2,ip3
a.com,ns1.a.com,ns2.a.com,
b.com,ns1.b.com,ns2.b.com,ns3.b.com
c.com,ns1.c.com,,

注意事项 #

• 确保IN.csv格式规范，避免数据末尾的多余空格（示例中c.com的IP后空格会被导入为数据的一部分，建议提前清理）。
• 系统自带的sqlite3需支持窗口函数（ROW_NUMBER()），Mac OS 10.14+、主流Linux发行版自带的sqlite3版本均满足该要求。

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