SQL Server 2008 R2 complicated view

I have been thinking about a complicated view/query I need to create but I can't seem to find a good (or working) solution to my problem. First I will give you a piece of my database structure to help explain my problem:

Every Agreement usually has 3 persons involved: a client, an endclient, and a contactperson. These role types are defined in the PersonAgreementInvolvementRole table. What I want is to create a view where I can get all three person names involved in the agreement, so something like this:

AgreementID  |  ClientName  |  EndClientName  |  ContactPersonName

          1  |  Company1    |     Company2    |     Smith

Because I need to go to the same (Person) table everytime to get the name of the person involved, I don't know how I can do this best. I first tried something like this:

select ag.StartDate, ag.EndDate, ag.PriceRate, ag.TaskDescription, pc.Name as ClientName, pec.Name as EndClien开发者_如何转开发tName
from dbo.Agreement ag inner join dbo.PersonAgreementInvolvement pai
on ag.AgreementID = pai.AgreementID
inner join dbo.Person pc
on pai.PersonID = pc.PersonID
inner join dbo.PersonAgreementInvolvementRole pairc
on pai.PersonAgreementInvolvementRoleID = pairc.PersonAgreementInvolvementRoleID
inner join dbo.Person pec
on pai.PersonID = pec.PersonID
inner join dbo.PersonAgreementInvolvementRole pairec
on pai.PersonAgreementInvolvementRoleID = pairec.PersonAgreementInvolvementRoleID
where pairec.Value = 'Client'
and
pairc.Value = 'EndClient'

but that didn't work (it returned no data at all), after that I tried a query with a union but that also didn't work.

Any of you have an idea? More information can be provided if needed, just ask!

Example data:

I have an agreement (AgreementID = 1) with two persons involved. There is a person with the name "Google" which has the InvolvementRole "Client" and another person named "Microsoft" which has the InvolvementRole "EndClient". What I would like to get from my query is the following:

AgreementID  -  Client  -  EndClient
-------------------- -----------------
1               Google     Microsoft

EDIT: SOLVED! Ultimately, this query did the job:

SELECT  ag.StartDate, ag.EndDate, ag.PriceRate, CAST( ag.TaskDescription AS VARCHAR(8000)),
        MIN(CASE WHEN pair.Value = 'Klant' THEN pc.Name END) as Klant, 
        MIN(CASE WHEN pair.Value = 'Eindklant' THEN pc.Name END) as Eindklant,
        MIN(CASE WHEN pair.Value = 'ContactPerson' THEN pc.Name END) as ContactPersonName
FROM dbo.Agreement ag
INNER JOIN dbo.PersonAgreementInvolvement pai 
ON ag.AgreementID = pai.AgreementID 
inner join dbo.PersonAgreementInvolvementRole pair
on pai.PersonAgreementInvolvementRoleID = pair.PersonAgreementInvolvementRoleID
LEFT JOIN dbo.Person pc
ON pai.PersonID = pc.PersonID 
GROUP BY ag.StartDate, ag.EndDate, ag.PriceRate, CAST( ag.TaskDescription AS VARCHAR(8000))

Thanks to everyone who helped me solve it! :)

If I understood your structure correctly, this should do the trick:

UPDATED QUERY

WITH PersonsInvolved AS
(
    SELECT  AgreementID,
            MIN(CASE WHEN pai.Value = 'Client' THEN pc.Name END) as ClientName,
            MIN(CASE WHEN pai.Value = 'EndClient' THEN pc.Name END) as EndClientName,
            MIN(CASE WHEN pai.Value = 'ContactPerson' THEN pc.Name END) as ContactPersonName
    FROM dbo.PersonAgreementInvolvement pai
    LEFT JOIN dbo.Person pc
    ON pai.PersonID = pc.PersonID
    GROUP BY AgreementID
)
SELECT  ag.StartDate, ag.EndDate, ag.PriceRate, ag.TaskDescription,
        pin.ClientName, pin.EndClientName, pin.ContactPersonName
FROM dbo.Agreement ag 
INNER JOIN PersonsInvolved pin
ON ag.AgreementID = pin.AgreementID

The code included in the question should work, although I would be inclined to change the joins to left outer joins (and move the where conditions to join conditions, as otherwise they will effectively turn outer joins back in to inner joins).

Here's a different approach:

select ag.AgreementID,
       max(ag.StartDate),
       max(ag.EndDate),
       max(ag.PriceRate),
       max(ag.TaskDescription),
       max(case parl.value when 'Client' then psn.name else '' end),
       max(case parl.value when 'EndClient' then psn.name else '' end),
       max(case parl.value when 'ContactPerson' then psn.name else '' end)
from dbo.Agreement ag 
left join dbo.PersonAgreementInvolvement pai      on ag.AgreementID = pai.AgreementID
left join dbo.Person psn                          on pai.PersonID = pc.PersonID
left join dbo.PersonAgreementInvolvementRole parl on pai.PersonAgreementInvolvementRoleID = parl.PersonAgreementInvolvementRoleID
group by ag.AgreementID

Alternatively:

select ag.AgreementID,
       ag.StartDate,
       ag.EndDate,
       ag.PriceRate,
       ag.TaskDescription,
       parl.value as role,
       psn.name
from dbo.Agreement ag 
left join dbo.PersonAgreementInvolvement pai      on ag.AgreementID = pai.AgreementID
left join dbo.Person psn                          on pai.PersonID = pc.PersonID
left join dbo.PersonAgreementInvolvementRole parl on pai.PersonAgreementInvolvementRoleID = parl.PersonAgreementInvolvementRoleID

- should show all roles that are actually being selected per agreement (but not on the same line).