SQL关于多行多列的转换
- 行转列
1） 基础数据准备（PS：由于懒得很，所以数据就是大家经常看到的，所以你懂得！）
2） 建表语句
IF OBJECT_ID('TB') IS NOT NULL
DROP TABLE TB
GO
CREATE TABLE TB
(
姓名 VARCHAR(10) ,
课程 VARCHAR(10) ,
分数 INT
)
INSERT INTO TB
VALUES ( '张三', '语文', 74 )
INSERT INTO TB
VALUES ( '张三', '数学', 83 )
INSERT INTO TB
VALUES ( '张三', '物理', 93 )
INSERT INTO TB
VALUES ( '李四', '语文', 74 )
INSERT INTO TB
VALUES ( '李四', '数学', 84 )
INSERT INTO TB
VALUES ( '李四', '物理', 94 )
INSERT INTO TB
VALUES ( '王五', '语文', 73 )
INSERT INTO TB
VALUES ( '王五', '数学', 88 )
INSERT INTO TB
VALUES ( '王五', '物理', 95 )
3） 查询效果
SELECT *
FROM TB
4） 进行行转列操作
- 传统的转换方式
SELECT 姓名 ,
MAX(CASE 课程
WHEN '语文' THEN 分数
ELSE 0
END) 语文 ,
MAX(CASE 课程
WHEN '数学' THEN 分数
ELSE 0
END) 数学 ,
MAX(CASE 课程
WHEN '物理' THEN 分数
ELSE 0
END) 物理
FROM TB
GROUP BY 姓名
- 使用函数
SELECT *
FROM TB PIVOT( MAX(分数) FOR 课程 IN ( 语文, 数学, 物理 ) ) AS T
但是如果想要实现如下的显示，使用函数是实现不了的
所以我们还是得用最原始的行转列的方式进行实现，具体实现代码如下
SELECT '语文' AS 课程 ,
MAX(CASE
WHEN 课程='语文' AND 姓名='李四' THEN 分数
ELSE 0
END) 李四 ,
MAX(CASE
WHEN 课程='语文' AND 姓名='王五' THEN 分数
ELSE 0
END) 王五 ,
MAX(CASE
WHEN 课程='语文' AND 姓名='张三' THEN 分数
ELSE 0
END) 张三
FROM TB
UNION ALL
SELECT '数学' AS 课程 ,
MAX(CASE
WHEN 课程='数学' AND 姓名='李四' THEN 分数
ELSE 0
END) 李四 ,
MAX(CASE
WHEN 课程='数学' AND 姓名='王五' THEN 分数
ELSE 0
END) 王五 ,
MAX(CASE
WHEN 课程='数学' AND 姓名='张三' THEN 分数
ELSE 0
END) 张三
FROM TB
UNION ALL
SELECT '物理' AS 课程 ,
MAX(CASE
WHEN 课程='物理' AND 姓名='李四' THEN 分数
ELSE 0
END) 李四 ,
MAX(CASE
WHEN 课程='物理' AND 姓名='王五' THEN 分数
ELSE 0
END) 王五 ,
MAX(CASE
WHEN 课程='物理' AND 姓名='张三' THEN 分数
ELSE 0
END) 张三
FROM TB
- 还有另外的一种行转列的方式（前提是基表的数据就已经是下面这种的）
SELECT * FROM TB1
如何实现上面的多行多列的转换捏？其实，还是得用最原始的方法
具体代码如下
SELECT '语文分数' AS 课程分数 ,
MAX(CASE
WHEN 姓名='张三' THEN 语文分数
ELSE 0
END) 张三 ,
MAX(CASE
WHEN 姓名='李四' THEN 语文分数
ELSE 0
END) 李四 ,
MAX(CASE
WHEN 姓名='王五' THEN 语文分数
ELSE 0
END) 王五
FROM TB1
UNION ALL
SELECT '数学分数' AS 课程分数 ,
MAX(CASE
WHEN 姓名='张三' THEN 数学分数
ELSE 0
END) 张三 ,
MAX(CASE
WHEN 姓名='李四' THEN 数学分数
ELSE 0
END) 李四 ,
MAX(CASE
WHEN 姓名='王五' THEN 数学分数
ELSE 0
END) 王五
FROM TB1
UNION ALL
SELECT '物理分数' AS 课程分数 ,
MAX(CASE
WHEN 姓名='张三' THEN 物理分数
ELSE 0
END) 张三 ,
MAX(CASE
WHEN 姓名='李四' THEN 物理分数
ELSE 0
END) 李四 ,
MAX(CASE
WHEN 姓名='王五' THEN 物理分数
ELSE 0
END) 王五
FROM TB1
运行之后的结果如下
