SQLite中如何将一个表的行与另一个表的列关联匹配？

要实现这种列转行并关联的操作，由于SQLite没有内置的UNPIVOT函数，我们可以通过UNION ALL手动将standards表的列转换为行结构，再与transactions表关联。

具体SQL语句 #

SELECT t.ID, t.Department, s.Standard
FROM transactions t
JOIN (
    -- 将standards表的列转为行，构造Department与Standard的对应关系
    SELECT 'IT' AS Department, IT AS Standard FROM standards
    UNION ALL
    SELECT 'Customer Service' AS Department, "Customer Service" AS Standard FROM standards
    UNION ALL
    SELECT 'Cleaning' AS Department, Cleaning AS Standard FROM standards
) s ON t.Department = s.Department;

逻辑说明 #

1. 子查询通过UNION ALL把standards表的每一列拆分为独立的行，每行包含部门名称和对应的标准值；
2. 用transactions表的Department字段与子查询的Department字段做等值关联，最终得到ID、部门名称、标准值的对应结果。

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