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