SQL sever 外连接行转列
1、建表插数据查询（需要对case有一定了解）
1 create table A 2 ( 3 s varchar(20), 4 b varchar(20) 5 ) 6 7 insert into a values('赤井','SQL入门') 8 insert into a values('赤井','UNIX基础') 9 insert into a values('铃木','SQL入门') 10 insert into a values('工藤','SQL入门') 11 insert into a values('工藤','Java中级') 12 insert into a values('吉田','UNIX基础') 13 insert into a values('渡边','SQL入门') 14 15 select s 名字,b 课程 from a
2、查询结果如下
| 名字 | 课程 |
| 赤井 |
SQL入门 |
| 赤井 |
UNIX基础 |
| 铃木 |
SQL入门 |
| 工藤 |
SQL入门 |
| 工藤 |
Java中级 |
| 吉田 |
UNIX基础 |
| 渡边 |
SQL入门 |
3、外连接行转列无脑写法1
1 select distinct a0.s 名字, 2 case when a1.b is not null then '○' else null end SQL入门, 3 case when a2.b is not null then '○' else null end UNIX基础, 4 case when a3.b is not null then '○' else null end Java中级 5 from a a0 6 left join 7 (select * from a where b='SQL入门') a1 8 on a0.s=a1.s left join 9 (select * from a where b='UNIX基础') a2 10 on a0.s=a2.s left join 11 (select * from a where b='Java中级') a3 12 on a0.s=a3.s
4、外连接行转列无脑写法2
1 select a0.s 名字, 2 (select '○' from a where b='SQL入门' and a0.s=a.s) as SQL入门, 3 (select '○' from a where b='UNIX基础' and a0.s=a.s) as UNIX基础, 4 (select '○' from a where b='Java中级' and a0.s=a.s) as Java中级 5 from (select distinct s from a) a0
5、灵活一点的写法，使用聚合对单表行转列（不使用group by会返回所有行数）
1 select s 姓名, 2 case when sum(case when b='SQL入门' then 1 else null end)=1 then '○' else null end as SQL入门, 3 case when sum(case when b='UNIX基础' then 1 else null end)=1 then '○' else null end as UNIX基础, 4 case when sum(case when b='Java中级' then 1 else null end)=1 then '○' else null end as Java中级 5 from a 6 group by s
查询出的结果都如下表
| 姓名 | SQL入门 | UNIX基础 | Java中级 |
| 赤井 | ○ | ○ | NULL |
| 渡边 | ○ | NULL | NULL |
| 工藤 | ○ | NULL | ○ |
| 吉田 | NULL | ○ | NULL |
| 铃木 | ○ | NULL | NULL |
