一个结果来自2行相同的表

I have the following table

我有下表

ID | Person | Type | Function
-----------------------------
1  | John   | 1    | 1
2  | Smith  | 1    | 2

I want do a query to get a single result from both rows. The first column is Person as ProjectLead where function = 1 and type = 1 and the second column is Person as Stakeholder where function = 2 and type = 1

我想做一个查询以从两行获得单个结果。第一列是Person作为ProjectLead,其中function = 1且type = 1,第二列是Person as Stakeholder,其中function = 2且type = 1

ProjectLead | Stakeholder
-----------------------------
John        | Smith

Does anyone have any suggestions?

有没有人有什么建议?

2 个解决方案

#1

You could use a self join on the type:

您可以在类型上使用自联接:

SELECT projectlead, stakeholder
FROM   (SELECT person AS projectlead, type
        FROM   mytable
        WHERE  function = 1) p
JOIN   (SELECT person AS stakeholder, type
        FROM   mytable
        WHERE  function = 2) s ON p.type = s.type

#2

SelfJoin on your table

SelfJoin在你的桌子上

SELECT  ProjectLead = ProjectLead.Person,
        Stakeholder = StakeHolder.Person
FROM [YourTableName] AS ProjectLead
LEFT JOIN [YourTableName] AS StakeHolder ON StakeHolder [Function] = 2 AND StakeHolder.[TYPE] = 1
WHERE ProjectLead.[Function] = 1 AND ProjectLead.[TYPE] = 1

#1