SQLServer 行列转置

列转行

数据准备

-- 建表
1> create table stuscore
2> (
3> name varchar(20),
4> subject varchar(20),
5> score int
6> );
7> go

-- 插入数据
1> insert into stuscore values ('zhangsan', 'ch', 70);
2> insert into stuscore values ('zhangsan', 'ma', 80);
3> insert into stuscore values ('zhangsan', 'en', 90);
4> insert into stuscore values ('lisi', 'ma', 60);
5> insert into stuscore values ('lisi', 'en', 50);
6> insert into stuscore values ('lisi', 'ch', 40);
7> insert into stuscore values ('wangwu', 'ma', 30);
8> insert into stuscore values ('wangwu', 'en', 20);
9> insert into stuscore values ('wangwu', 'ch', 10);
10> go

--查看数据
1> select * from stuscore;
2> go
name                 subject              score      
-------------------- -------------------- -----------
zhangsan             ch                            70
zhangsan             ma                            80
zhangsan             en                            90
lisi                 ma                            60
lisi                 en                            50
lisi                 en                            50
lisi                 ch                            40
wangwu               ma                            30
wangwu               en                            20
wangwu               ch                            10

方法一（CASE WHEN）

1> select name as 'xm',
2> max(case subject when 'ch' then score else 0 end) as 'ch',
3> max(case subject when 'en' then score else 0 end) as 'en',
4> max(case subject when 'ma' then score else 0 end) as 'ma',
5> sum(score) as 'zf',
6> avg(score) as 'pjf'
7> from stuscore
8> group by name;
9> go

结果

方法二（PIVOT函数）

1> select pvt.name as 'xm', pvt.ch, pvt.ma, pvt.en from stuscore 
2> pivot(max(score) for subject in (ch, en, ma)) as pvt;
3> go

结果

行转列

数据准备

-- 建表
1> create table score
2> (
3> name varchar(20),
4> ch int,
5> en int,
6> ma int
7> );
8> go

-- 插入数据
1> insert into score values ('zhangsan', 60, 70, 80);
2> insert into score values ('lisi', 50, 40, 30);
3> insert into score values ('wangwu', 30, 20, 10);
4> go

-- 查看数据
1> select * from score;
2> go
name                 ch          en          ma         
-------------------- ----------- ----------- -----------
zhangsan                      60          70          80
lisi                          50          40          30
wangwu                        30          20          10

方法一（UNION ALL）

1> select * from (
2> select name as 'xm', 'ch' as 'subject', ch as 'cj' from score
3> union all
4> select name as 'xm', 'ma' as 'subject', ma as 'cj' from score
5> union all
6> select name as 'xm', 'en' as 'subject', en as 'cj' from score
7> ) t
8> go

--结果
xm                   subject cj         
-------------------- ------- -----------
zhangsan             ch               60
lisi                 ch               50
wangwu               ch               30
zhangsan             ma               80
lisi                 ma               30
wangwu               ma               10
zhangsan             en               70
lisi                 en               40
wangwu               en               20
(9 rows affected)

方法二（UNPIVOT函数）

1> select * from score unpivot (score for subject in (ch, ma, en)) t
2> go

结果