1、行转列 微软官方的图：
方法二
DROP TABLE studentB;
CREATE TABLE studentB (
姓名 varchar(10),
语文 varchar(10),
数学 varchar(10),
物理 varchar(10)
);
INSERT INTO studentB
VALUES (‘张三‘, 75, 83, 96),
(‘李四‘, 74, 84, 95);
SELECT *
FROM (
SELECT 姓名, ‘语文‘ AS 科目, 语文 AS 分数
FROM studentB
UNION ALL
SELECT 姓名, ‘数学‘ AS 科目, 数学 AS 分数
FROM studentB
UNION ALL
SELECT 姓名, ‘物理‘ AS 科目, 物理 AS 分数
FROM studentB
) a
2： 列转行
怎么把一条记录拆分成几条记录？
User No. A B C
1 1 21 34 24
1 2 42 25 16
RESULT:
User No. Type Num
1 1 A 21
1 1 B 34
1 1 C 24
1 2 A 42
1 2 B 25
1 2 C 16
declare @t table(usser int ,no int ,a int,b int, c int)
insert into @t select 1,1,21,34,24
union all select 1,2,42,25,16
SELECT usser,no,Type=attribute, Num=value
FROM @t
UNPIVOT
(
value FOR attribute IN([a], [b], [c])
) AS UPV
--结果
/*
usser no Type num
---- --- -------- --------
1 1 a 21
1 1 b 34
1 1 c 24
1 2 a 42
1 2 b 25
1 2 c 16
*/
方法二
-- 列传行
drop table studentA;
create table studentA (name varchar(10) ,subject varchar(10) ,score int) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into studentA values (‘张三‘,‘语文‘,75),(‘张三‘,‘数学‘,83),(‘张三‘,‘物理‘,96);
insert into studentA values (‘李四‘,‘语文‘,74),(‘李四‘,‘数学‘,84),(‘李四‘,‘物理‘,95);
select name 姓名,
max(case subject when ‘语文‘ then score end) 语文,
max(case subject when ‘数学‘ then score end) 数学,
max(case subject when ‘物理‘ then score end) 物理
from studentA
group by name
原文：https://www.cnblogs.com/nfqd/p/9964865.html