sql server中的 行转列

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

 

转载于:https://www.cnblogs.com/wslm/p/3993347.html