SQL中行列转换Pivot
2024-10-12 06:15:18
数据
CREATE TABLE student(
no int,
ca varchar(20),
name varchar(50),
subject varchar(50),
scorce int
); /* 数据 */
INSERT INTO student VALUES(1, '1班', '张三', '语文', 85);
INSERT INTO student VALUES(2, '1班', '张三', '数学', 90);
INSERT INTO student VALUES(3, '1班', '张三', '英语', 70);
INSERT INTO student VALUES(4, '1班', '李四', '语文', 70);
INSERT INTO student VALUES(5, '1班', '李四', '数学', 99);
INSERT INTO student VALUES(6, '1班', '李四', '英语', 62);
INSERT INTO student VALUES(7, '1班', '王五', '语文', 82);
INSERT INTO student VALUES(8, '1班', '王五', '数学', 74);
INSERT INTO student VALUES(9, '1班', '王五', '英语', 89);
INSERT INTO student VALUES(10, '2班', '刘晓希', '语文', 77);
INSERT INTO student VALUES(11, '2班', '刘晓希', '数学', 99);
INSERT INTO student VALUES(12, '2班', '刘晓希', '英语', 80);
INSERT INTO student VALUES(13, '2班', '朱鹏', '语文', 87);
INSERT INTO student VALUES(14, '2班', '朱鹏', '数学', 86);
INSERT INTO student VALUES(15, '2班', '朱鹏', '英语', 76);
INSERT INTO student VALUES(16, '2班', '欧阳雪', '语文', 91);
INSERT INTO student VALUES(17, '2班', '欧阳雪', '数学', 83);
INSERT INTO student VALUES(18, '2班', '欧阳雪', '英语', 77);
行转列
-- case when 实现
SELECT S.ca,
SUM(CASE WHEN S.subject='语文' THEN S.scorce ELSE 0 END) AS '语文',
SUM(CASE WHEN S.subject='数学' THEN S.scorce ELSE 0 END) AS '数学',
SUM(CASE WHEN S.subject='英语' THEN S.scorce ELSE 0 END) AS '英语'
FROM dbo.student AS S
GROUP BY S.ca
--pivot 实现
SELECT a.ca,
[语文],
[数学],
[英语]
INTO #StudentGroup --导入临时表当中,UNPIVOT例子使用
FROM
(
SELECT S.ca,
S.subject,
SUM(S.scorce) AS scorce
FROM dbo.student AS S
GROUP BY S.ca,
subject
) AS S1
PIVOT
(
SUM(S1.scorce)
FOR S1.subject IN ([语文], [数学], [英语])
) a;
列转行
-- UNPIVOT 实现
SELECT a.ca,
a.科目,
a.分数
FROM #StudentGroup AS SG UNPIVOT(分数 FOR 科目 IN([语文], [数学], [英语])) AS a;
最新文章
- HDU 5458 Stability(双连通分量+LCA+并查集+树状数组)(2015 ACM/ICPC Asia Regional Shenyang Online)
- iOS基于MBProgressHUD的二次封装,一行搞定,使用超简单
- fireworks将图片变为透明色
- 【iOS开发必备指南合集】申请企业级IDP、真机调试、游戏接入GameCenter 指南(实现仿官方的成就提示)、游戏接入OpenFeint指南;
- DEDECMS中,会员中心的常用知识
- emulatorarm.exe已停止工作
- SpringMVC学习 DispatcherServlet (转载)
- HDU1711-----Number Sequence-----裸的KMP
- [Android开发常见问题-11] Unable to execute dex: Multiple dex files define 解决方法
- 矢量切片(Vector tile)
- Java使用Aspose组件进行多文档间的转换操作
- ArcGIS for JavaScript学习(一)
- GateOne Web SSH 环境搭建
- Java 数据库操作
- 前端 搜索样式 html
- [hgoi#2019/2/24]玄学考试
- python3 urllib模块使用
- windows清除访问共享文件夹的登陆信息
- Linux SSH免密登录
- C语言读取配置文件