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

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;

   

用PIVOT语句，将行转换成列，语句如下：

    --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;

运行结果如下：

    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;

下面将列转换成行：

    --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;