SQL Server 2005 PivotTable Question

Question

I have a set of Entity-Value values ​​and would like to collapse the results. Here's the effect I'm looking for, except that SELECT stmt using common table expressions doesn't work correctly. My question is: am I on the right track, or is there some simple summary command?

USE TempDB
Declare @Person TABLE(
PersonID Int Identity(101,1))
INSERT INTO @Person DEFAULT VALUES
INSERT INTO @Person DEFAULT VALUES
INSERT INTO @Person DEFAULT VALUES
INSERT INTO @Person DEFAULT VALUES

DECLARE @Attribute TABLE(
AttributeID Int Identity(10,1) PRIMARY KEY,
AttributeName Varchar(MAX))
INSERT INTO @Attribute(AttributeName) VALUES('Firstname')
INSERT INTO @Attribute(AttributeName) VALUES('Lastname')

DECLARE @Pat TABLE( -- A Person Attributes
PatID Int Identity,
PersonID Int,
AttributeID Int,
PatValue Varchar(MAX)
)
INSERT INTO @Pat(PersonID,AttributeID,PatValue) VALUES(101,10,'John')
INSERT INTO @Pat(PersonID,AttributeID,PatValue) VALUES(102,10,'Paul')
INSERT INTO @Pat(PersonID,AttributeID,PatValue) VALUES(103,10,'George')
INSERT INTO @Pat(PersonID,AttributeID,PatValue) VALUES(104,10,'Ringo')
INSERT INTO @Pat(PersonID,AttributeID,PatValue) VALUES(101,11,'Lennon')
INSERT INTO @Pat(PersonID,AttributeID,PatValue) VALUES(102,11,'McCartney')
INSERT INTO @Pat(PersonID,AttributeID,PatValue) VALUES(103,11,'Harrison')

SELECT Pat.PersonID,AttributeName,PatValue
FROM @Pat Pat
INNER JOIN @Person Person
ON Pat.PersonID = Person.PersonID
INNER JOIN @Attribute Attribute
ON Pat.AttributeID=Attribute.AttributeID
-- 

;WITH CTE1 AS(
    SELECT PersonID,PatValue AS FirstName
    FROM @Pat Pat
    INNER JOIN @Attribute Attribute
    ON Pat.AttributeID=Attribute.AttributeID
    WHERE AttributeName='FirstName'
)
,CTE2 AS(
    SELECT PersonID,PatValue AS LastName
    FROM @Pat Pat
    INNER JOIN @Attribute Attribute
    ON Pat.AttributeID=Attribute.AttributeID
    WHERE AttributeName='LastName'
)
SELECT Pat.PersonID,FirstName,LastName
FROM @Pat Pat
LEFT OUTER JOIN CTE1
ON Pat.PersonID=CTE1.PersonID
LEFT OUTER JOIN CTE2
ON Pat.PersonID=CTE2.PersonID
ORDER BY PersonID

I just need a list of 4 people with their name and surname.

Answer 1

Good example here.

The request in your case will look like this:

SELECT PersonId, FirstName, LastName
FROM 
(
    -- your query
    SELECT Pat.PersonID,AttributeName,PatValue
    FROM @Pat Pat
    INNER JOIN @Person Person
    ON Pat.PersonID = Person.PersonID
    INNER JOIN @Attribute Attribute
    ON Pat.AttributeID=Attribute.AttributeID
) up
PIVOT (MAX(PatValue) FOR AttributeName IN (FirstName, LastName)) AS pvt