SQL Server??PIVOT????????????????

???????????????????????SQL Server?????????T-SQL?????——??SQL Server 2005?????PIVOT????????????????????????????SQL Server??Σ??????——??????????????????????????????????????????????????????SQL Server???PIVOT??????????????????????
????????
????SQL Server??PIVOT????????????????????T-SQL??????????????????С????????????SQL Server 2005???????????????????????????????????Entity Attribute Value model (EAV)?????????????EAM??????????????????????????壬???????????????????????????EAV???洢???????????????/???洢?????????????????????????????/?????????
CREATE TABLE EAVTable
(
RecordID INT NOT NULL??
Element CHAR(100) NOT NULL??
Value SQL_VARIANT NOT NULL??
PRIMARY KEY (RecordID?? Element)
)
GO
-- Insert some records
INSERT INTO EAVTable (RecordID?? Element?? Value) VALUES
(1?? 'FirstName'?? 'Woody')??
(1?? 'LastName'?? 'Tu')??
(1?? 'City'?? 'Linhai')??
(1?? 'Country'?? 'China')??
(2?? 'FirstName'?? 'Bill')??
(2?? 'LastName'?? 'Gates')??
(2?? 'City'?? 'Seattle')??
(2?? 'Country'?? 'USA')
GO
?????????????????????2?????????嵽?????????????????????????????????????????????????????????????????????????????????????????б????????????????——????????????????“????”……
?????????????EAV?????????????????????????/??????????????????????????????????С????????????????PIVOT?????????????????????????????CASE???????д???????????????????PIVOT??????????????????????????T-SQL???ЩCASE??????????????????????????????T-SQL?????????3????Σ?
?????????Σ?Grouping Phase??
?????????Σ?Spreading Phase??
????????Σ?Aggregation Phase??
??????????Σ?Grouping Phase??????????????EAV???????????????塣????????????RecordID?н??????GROUP BY?????2??ε???????Σ?Spreading Phase?????????????CASE?????????????С????????Σ?Aggregation Phase?????????MAX???????????к??з????????????????????T-SQL??????
-- Pivot the data with a handwritten T-SQL statement.
-- Make sure you have an index defined on the grouping column.
SELECT
RecordID??
-- Spreading and aggregation phase
MAX(CASE WHEN Element = 'FirstName' THEN Value END) AS 'FirstName'??
MAX(CASE WHEN Element = 'LastName' THEN Value END) AS 'LastName'??
MAX(CASE WHEN Element = 'City' THEN Value END) AS 'City'??
MAX(CASE WHEN Element = 'Country' THEN Value END) AS 'Country'
FROM EAVTable
GROUP BY RecordID
-- Grouping phase
GO
??????????????????????????????????Σ???????????????T-SQL?????????????????????????????????????С?

????PIVOT?????
??????SQL Server 2005??????10???????????????T-SQL??????PIVOT?????????????????????????????????????е??У???????????????????ɡ???????????????????????????????д??????????????PIVOT???????????????????
????-- Perform the same query with the native PIVOT operator.
????-- The grouping column is not specified explicitly?? it's the remaining column
????-- that is not referenced in the spreading and aggregation elements.
????SELECT
????RecordID??
????FirstName??
????LastName??
????City??
????Country
????FROM EAVTable
????PIVOT(MAX(Value) FOR Element IN (FirstName?? LastName?? City?? Country)) AS t
????GO
???????????????????????????????????????????????PIVOT???????????????????????????????????????
?????????????????????????????????????????
?????????????????PIVOT???????????????????С???????????????????????PIVOT??????????????RecordID?У???????????????Σ?Grouping Phase??????á??????????????????????????????????????????????????????????У?
????-- Add a new column to the table
????ALTER TABLE EAVTable ADD SomeData CHAR(1)
????GO