MSSQL2005行列转换(使用PIVOT and UNPIVOT)

时间:2022-01-09 00:46:55

PIVOT syntax:

       SELECT ..... FROM pivoted_table        pivoted_table ::= table_source PIVOT <pivot_clause> table_alias        table_source ::= table,view, sub-query,XML......, 即SQL语句From关键字后可以跟的所有东西,定义太复杂,这里就不写了        pivot_clause ::=aggregate_function ( value_column ) 
                                    FOR pivot_column 
                                    IN ( <column_list> )
                                 )
     UNPIVOT syntax:       SELECT ..... FROM unpivoted_table       unpivoted_table ::= table_source UNPIVOT <unpivot_clause> table_alias       table_source ::= Table, view, sub-query, XML..., 即SQL语句From关键字后可以跟的所有东西,定义太复杂,这里就不写了       unpivot_clause ::= ( value_column FOR pivot_column IN ( <column_list> ) )
    PIVOT example:
    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 * FROM Score;      StuNo StuName CourseName Score
      1     Tom     Math       80
      1     Tom     English    82
      1     Tom     Geography  84
      2     Jone    Math       79
      2     Jone    English    88
      2     Jone    Geography  86
     --Change row to column:
    SELECT StuNo, StuName, Math, English, Geography
    FROM Score
    PIVOT ( MAX(Score) FOR CourseName in (Math, English, Geography) ) AS ScoreList
    ORDER BY StuNo;
     StuNo  StuName  Math English  Geography
      1     Tom      80    82       84
      2     Jone     79    88       86
     DROP TABLE Score;
    GO
    UNPIVOT example:
    CREATE TABLE ScoreList ( StuNo varchar(10), StuName varchar(50), Math int, English int, Geography int);
    GO
    INSERT INTO ScoreList (StuNo, StuName, Math, English, Geography) VALUES ('1', 'Tom', 80, 82, 84);
    INSERT INTO ScoreList (StuNo, StuName, Math, English, Geography) VALUES ('2', 'Jone', 79, 88, 86);
    GO
     SELECT * FROM ScoreList;     StuNo  StuName  Math English  Geography
      1     Tom      80    82       84
      2     Jone     79    88       86
     --Change column values to row:
    SELECT StuNo, StuName, CourseName, Score
    FROM ScoreList
    UNPIVOT ( Score FOR CourseName in (Math, English, Geography) ) AS ScorePvtTable
    ORDER BY StuNo;
     StuNo StuName CourseName Score
      1     Tom     Math       80
      1     Tom     English    82
      1     Tom     Geography  84
      2     Jone    Math       79
      2     Jone    English    88
      2     Jone    Geography  86
     DROP TABLE ScoreList;
    GO