SQL行转列需求：编写SELECT语句将ID列条目转为新列

你的需求是把原表中ID列的每个值转为单独的列，同时让X、Y坐标作为所有新列的共享主键，这属于典型的行转列（透视表）场景。由于SQLite没有内置的PIVOT函数，我们可以用条件聚合的方式实现。

核心SQL语句 #

SELECT
    X,
    Y,
    MAX(CASE WHEN ID = 'ID1' THEN VAL END) AS ID1,
    MAX(CASE WHEN ID = 'ID2' THEN VAL END) AS ID2,
    MAX(CASE WHEN ID = 'ID3' THEN VAL END) AS ID3
FROM test
GROUP BY X, Y
ORDER BY X, Y DESC;

逻辑说明 #

1. 条件筛选：通过CASE WHEN语句，针对每个ID值提取对应的VAL，不匹配的ID会返回NULL
2. 聚合去重：使用MAX()（或MIN()，因为每个(X,Y,ID)组合仅对应一个VAL）聚合，确保每个(X,Y)组只保留对应ID的有效VAL值
3. 分组主键：GROUP BY X, Y保证X、Y作为共享列，把同一坐标下不同ID的VAL聚合到同一行

结合你的测试代码验证 #

在你的Python代码末尾添加以下代码，即可得到目标结果：

# 执行透视查询
cur.execute("""
SELECT
    X,
    Y,
    MAX(CASE WHEN ID = 'ID1' THEN VAL END) AS ID1,
    MAX(CASE WHEN ID = 'ID2' THEN VAL END) AS ID2,
    MAX(CASE WHEN ID = 'ID3' THEN VAL END) AS ID3
FROM test
GROUP BY X, Y
ORDER BY X, Y DESC;
""")

# 获取并打印结果
result_columns = [desc[0] for desc in cur.description]
result_data = cur.fetchall()

print("结果列名:", result_columns)
print("结果数据:", result_data)

动态ID场景扩展 #

如果你的ID值是动态不确定的，需要用Python动态生成SQL语句：

# 获取所有唯一ID
cur.execute("SELECT DISTINCT ID FROM test")
ids = [row[0] for row in cur.fetchall()]

# 生成动态SQL
case_clauses = ",\n    ".join([f"MAX(CASE WHEN ID = '{id}' THEN VAL END) AS {id}" for id in ids])
sql = f"""
SELECT
    X,
    Y,
    {case_clauses}
FROM test
GROUP BY X, Y
ORDER BY X, Y DESC;
"""

# 执行动态SQL
cur.execute(sql)
result = cur.fetchall()

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