MSSQL2005行列转换(使用PIVOT and UNPIVOT)
来源:互联网 发布:怎么开通淘宝店铺 编辑:程序博客网 时间:2024/05/09 20:08
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
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;
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
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
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;
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
GO
- MSSQL2005行列转换(使用PIVOT and UNPIVOT)
- PIVOT/UNPIVOT行列转换
- Pivot UnPivot 行列转换
- Pivot UnPivot 行列转换
- SQl2005行列转换 pivot/unpivot
- SQLServer行列转换 Pivot UnPivot
- SQLServer行列转换 Pivot UnPivot
- SQL行列转换PIVOT、UNPIVOT
- [MSSQL]行列转换 Pivot UnPivot
- SQLServer行列转换 Pivot UnPivot
- SQLServer行列转换 Pivot UnPivot
- SQLServer行列转换 Pivot UnPivot
- SQL中行列转换 Pivot UnPivot 行列转化与PIVOT、UNPIVOT运算符使用
- 使用 PIVOT 和 UNPIVOT(MS SQL Server行列转换)
- PIVOT/UNPIVOT的用法(行列转换)
- SQL Server 行列转换 Pivot UnPivot
- sql server 行列转换PIVOT 和 UNPIVOT
- SQL通过PIVOT/UNPIVOT实现行列转换
- 架构师
- 数据库日志问题
- PowerDesigner
- 脚跟脚设计单件模式—第二脚
- 达成铁路铺轨随拍(图)
- MSSQL2005行列转换(使用PIVOT and UNPIVOT)
- 完美的Dos批处理实现sleep
- Linux平台gcc及动态库和静态库
- 【转】 使用XML和脚本创建模块化的游戏 第一部分
- 又见 邮局(图)
- 人月神话
- 孙鑫讲Java中文乱码问题的解决方案
- 推翻美狗谬论,正确评价金正日 -CSDN项有建
- Head First C# 中文版 第12章 回顾与前瞻 page527