问连接3个表

EN

--Creating the source tables
DECLARE @ATTRIBUTE TABLE
(   PeopleAttrCODE INT PRIMARY KEY CLUSTERED,
    PersonCODE INT,
    AttributeCODE  VARCHAR(50),
    Value VARCHAR(50))
INSERT INTO @Attribute(PeopleAttrCODE,PersonCODE,AttributeCODE,Value)
VALUES (1,1,'End Date','20151231'),
       (2,1,'Start Date','20150101'),
       (3,1,'Employee Type','Consultant'),
       (4,2,'End Date','20151231'),
       (5,2,'Start Date','20150101'),
       (6,2,'Employee Type','Employee')

DECLARE @IDENTIFIER TABLE
(   PeopleIDCODE  INT PRIMARY KEY CLUSTERED,
    PersonCODE INT,
    IdentifierCODE VARCHAR(50))
INSERT INTO @IDENTIFIER(PeopleIDCODE,PersonCODE,IdentifierCODE)
VALUES(1,1,'AAA'),
      (2,2,'BBB')

DECLARE @OBJECT TABLE
( ObjectCODE INT PRIMARY KEY CLUSTERED,
  Name VARCHAR(50))
INSERT INTO @OBJECT(ObjectCODE,Name)
VALUES (1,'User 1'),
       (2,'User 2')

SELECT
    OBJ.Name,
    ID.PersonCODE,
    ID.IdentifierCODE,
    CA.[Employee Type] AS [Type],
    CA.[Start Date] AS [Start Date],
    CA.[End Date] AS [End Date]
    --*
FROM
    @OBJECT AS OBJ
    INNER JOIN @IDENTIFIER AS ID
    ON OBJ.ObjectCODE = ID.PeopleIDCODE
    --ON OBJ.ObjectCODE = ID.PeopleCODE 
    INNER JOIN 
    (SELECT 
            PersonCODE
            ,A.[Start Date]
            , A.[End Date]
            ,A.[Employee Type]
     FROM 
        (SELECT PersonCODE,AttributeCODE,Value 
        FROM @ATTRIBUTE
        WHERE AttributeCODE IN ('End Date','Start Date','Employee Type')) AS ATT
        PIVOT
        ( MAX(Value) FOR AttributeCODE IN ([End Date],[Start Date],[Employee Type]))A    
    )CA
    ON CA.PersonCODE = ID.PersonCODE

Name       PersonCODE  IdentifierCODE Type            Start Date      End Date
User 1     1           AAA            Consultant      20150101        20151231
User 2     2           BBB            Employee        20150101        20151231