mssql2005行转成列

CREATE TABLE Score(StuNo varchar(10), StuName varchar(50), CourseName varchar(50), 

Score int);

GO 

INSERT INTO Score

                     (StuNo, StuName, CourseName, Score)

               VALUES ('1', 'Tom', 'Math', 80);

                         INSERT 

                       INTO Score(StuNo, StuName, CourseName, Score)

VALUES ('1', 'Tom', 'English', 82);

          INSERT 

        INTO Score(StuNo, StuName, CourseName, Score)

VALUES ('1', 'Tom', 'Geography', 84);

          INSERT 

        INTO Score(StuNo, StuName, CourseName, Score)

VALUES ('2', 'Jone', 'Math', 79);

          INSERT 

        INTO Score(StuNo, StuName, CourseName, Score)

VALUES ('2', 'Jone', 'English', 88);

          INSERT 

        INTO Score(StuNo, StuName, CourseName, Score)

VALUES ('2', 'Jone', 'Geography', 86);

 GO

 

 

--行转换成列

SELECT StuNo, StuName, Math, English, Geography

FROM Score PIVOT (MAX(Score) FOR CourseName IN (Math, English, Geography)) 

      AS ScoreList

ORDER BY StuNo;