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
?????????????丳???
????UPDATE dbo.EAVTable SET SomeData=LEFT(CAST(Value AS VARCHAR(1))??1)
????????????????PIVOIT????????????????????somedata?ж??з?NULL????????????????????????????????????????RecordID??SomeData?У????????????

????????????????????????????д?????T-SQL??????????????????????????????????????SQL Server??PIVOT???????????????????????????????????????塣???????????????????????????????е????????????????????????????????????????????????????????????????л?????з???????????????е????
????-- Use a table expression to state explicitly which columns you want to
????-- return from the base table. Therefore you can always control on which
????-- columns the PIVOT operator is performing the grouping.
????SELECT
????RecordID??
????FirstName??
????LastName??
????City??
????Country
????FROM
????(
????-- Table Expression
????SELECT RecordID?? Element?? Value FROM EAVTable
????) AS t
????PIVOT(MAX(Value) FOR Element IN (FirstName?? LastName?? City?? Country)) AS t1
????GO
?????????????????????????????????????PIVOT???????????????????????????????????С?????ζ??????????????????????PIVOT?????????
????С??
????????????????????????????SQL Server?????PIVOT?????????Σ??????????????????????Ч???????????????????????????????????????????????????????????????????PIVOT?????????????????????????????
??????PIVOT???????????????????????????????????????????????????