sql server中的 行轉列

標籤：style   blog   color   io   ar   strong   for   sp   div   

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

 

sql server中的 行轉列