以下是使用条件聚集的一种方法：

SELECT
    tableA.*,
    MAX(objective = 'objective1' AND is_core) AS objective1,
    MAX(objective = 'objective2' AND is_core) AS objective2
FROM tableA
JOIN tableB USING (project_id)
GROUP BY project_id;

我们必须稍微猜测一下，因为并不是100%清楚project Aobjective1的三行如何Map到objective1列中的值。
此查询检查is_core以查找最新阶段：

SELECT project_id, name,
    MAX(objective = 'objective1' AND is_core) AS objective1,
    MAX(objective = 'objective2' AND is_core) AS objective2
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY project_id, objective ORDER BY phase DESC) rn
    FROM tableA
    JOIN tableB USING (project_id)
) t
WHERE rn = 1
GROUP BY project_id;

SELECT *,
       EXISTS ( SELECT NULL
                FROM tableB 
                WHERE tableB.project_id = tableA.project_id
                   AND tableB.objective = 'objective1' ) objective1,
       EXISTS ( SELECT NULL
                FROM tableB 
                WHERE tableB.project_id = tableA.project_id
                   AND tableB.objective = 'objective2' ) objective2
FROM tableA;

如果目标的数量（以及最终列的数量）是不确定的（即您在PIVOT中需要），则使用动态SQL。