1

I have 3 tables as per below:

**ATTRIBUTE TABLE**
PeopleAttrCODE  PersonCODE  AttributeCODE   Value
      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

**IDENTIFIER TABLE**
PeopleIDCODE    PersonCODE  IdentifierCODE
      1             1       AAA
      2             2       BBB

**OBJECT TABLE**

ObjectCODE  Name
      1     User 1
      2     User 2

How can i view it as:

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

Thanks, Mel

1

Here is a way to do this. I hope , the join are on the right fields.

--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

The output:

Name       PersonCODE  IdentifierCODE Type            Start Date      End Date
User 1     1           AAA            Consultant      20150101        20151231
User 2     2           BBB            Employee        20150101        20151231
  • many thanks @sabin bio... but we have hundreds of entry on our attribute tables. :( – Mel Q Oct 19 '15 at 9:35
  • OK, you can filter it by adding a` WHERE` clause to the SELECT PersonCODE,AttributeCODE,Value FROM @ATTRIBUTE + WHERE AttributeCODE IN ('End Date','Start Date','Employee Type') – Sabin Bio Oct 19 '15 at 12:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.