CODEWITHSUNDEEP
sqlsql-serverselect
Hrvoje Miljak
Hrvoje Miljak

Reputation: 2443

One result from 2 rows same table

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

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

Does anyone have any suggestions?

Upvotes: 1

Views: 68

Answers (2)

AXMIM
AXMIM

Reputation: 2472

SelfJoin on your table

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

Upvotes: 0

Mureinik
Mureinik

Reputation: 311393

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

Upvotes: 1

Related Questions