SQL Server 2005 数据透视表问题

Question

我有一组实体值表，想要将结果进行透视。这是我想要的效果，但你会发现使用公共表达式的SELECT语句还没有完全正确地工作。我的问题是：我走在正确的轨道上吗？还是有一些简单的透视命令可以使用？

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

我只需要一个包含4个人的列表，他们的名字和姓氏即可。

- Phillip Senn

---

请提供实际的测试代码以重现问题。 - KM.

Answer 1

一个好的例子在这里

在您的情况下，查询看起来像这样:

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

- Lukasz Lysik

---

在pivot子句中，你必须硬编码“FirstName”和“LastName”。如果你想要检索和旋转的属性只能在运行时确定，这将变得困难。 - Philip Kelley