行转列函数PIVOT使用

PIVOT函数用于将某一列的列值旋转为列名，即行转列;
这个操作也成为'数据透视，就是在交叉表中将数据的行旋转为列.
'行转列'在做报表时经常用到，在进行复杂的查询统计，如销售统计，处理大量数据的时候。

语法说明：
PIVOT(<聚合函数>(<要聚合的列>), FOR col IN(constant))

## 可以聚合多列，只支持唯一列值旋转为列
PIVOT(<聚合函数>(<要聚合的列>)，FOR(col1,col2,…)IN((constant1,constant2,…)，…))

--学生成绩 sc表
drop table sc;
create table sc(
sname varchar(10),
cname varchar(10),
score int
) ;

gbase> create table sc(sname varchar(10), cname varchar(10), score int) ;
Query OK, 0 rows affected (Elapsed: 00:00:00.05)

insert into sc values ('张三','语文',90),('李四','语文',90),('张三','数学',85),('李四','数学',91),('张三','英语',72),('李四','英语',82);
gbase> select * from sc;
+--------+--------+-------+
| sname  | cname  | score |
+--------+--------+-------+
| 张三   | 语文   |    90 |
| 李四   | 语文   |    90 |
| 张三   | 数学   |    85 |
| 李四   | 数学   |    91 |
| 张三   | 英语   |    72 |
| 李四   | 英语   |    82 |
+--------+--------+-------+
6 rows in set (Elapsed: 00:00:00.03)
gbase> select * from sc PIVOT(sum(score) for cname in ('语文','数学'));
+--------+--------+--------+
| sname  | 语文   | 数学   |
+--------+--------+--------+
| 张三   |     90 |     85 |
| 李四   |     90 |     91 |
+--------+--------+--------+
2 rows in set (Elapsed: 00:00:00.95)

gbase> select * from sc PIVOT(sum(score) as S  for cname in ('语文','数学'));
+--------+----------+----------+
| sname  | 语文_S   | 数学_S   |
+--------+----------+----------+
| 张三   |       90 |       85 |
| 李四   |       90 |       91 |
+--------+----------+----------+
2 rows in set (Elapsed: 00:00:00.06)

gbase> select * from sc PIVOT(sum(score) for cname in ('语文' YUWEN,'数学'));
+--------+-------+--------+
| sname  | YUWEN | 数学   |
+--------+-------+--------+
| 张三   |    90 |     85 |
| 李四   |    90 |     91 |
+--------+-------+--------+
2 rows in set (Elapsed: 00:00:00.06)

gbase> select * from sc PIVOT(sum(score) S for cname in ('语文' as YUWEN,'数学'));
+--------+---------+----------+
| sname  | YUWEN_S | 数学_S   |
+--------+---------+----------+
| 张三   |      90 |       85 |
| 李四   |      90 |       91 |
+--------+---------+----------+
2 rows in set (Elapsed: 00:00:00.06)

gbase> insert into sc_1 (sname,cname) values ('王五','语文'),('王五','英语'),('王五','数学');
Query OK, 3 rows affected (Elapsed: 00:00:00.05)
Records: 3  Duplicates: 0  Warnings: 0

gbase> update sc_1 set score = 89 where sname='王五' and cname = '数学';
Query OK, 1 row affected (Elapsed: 00:00:00.06)
Rows matched: 1  Changed: 1  Warnings: 0

gbase> select * from sc_1;
+--------+--------+-------+
| sname  | cname  | score |
+--------+--------+-------+
| 张三   | 语文   |    90 |
| 李四   | 语文   |    90 |
| 张三   | 数学   |    85 |
| 李四   | 数学   |    91 |
| 张三   | 英语   |    72 |
| 李四   | 英语   |    82 |
| 王五   | 语文   |  NULL |
| 王五   | 英语   |  NULL |
| 王五   | 数学   |    89 |
+--------+--------+-------+
9 rows in set (Elapsed: 00:00:00.01)
gbase> select * from sc_1 PIVOT(sum(score) for cname in ('语文','数学'));
+--------+--------+--------+
| sname  | 语文   | 数学   |
+--------+--------+--------+
| 张三   |     90 |     85 |
| 李四   |     90 |     91 |
| 王五   |   NULL |     89 |
+--------+--------+--------+
3 rows in set (Elapsed: 00:00:00.04)

gbase> select * from sc_2;                                              
+--------+--------+--------+-------+
| sno    | sname  | cname  | score |
+--------+--------+--------+-------+
| 202501 | 张三   | 语文   |    90 |
| 202502 | 李四   | 语文   |    90 |
| 202503 | 张三   | 数学   |    85 |
| 202502 | 李四   | 数学   |    91 |
| 202501 | 张三   | 英语   |    72 |
| 202502 | 李四   | 英语   |    82 |
|   NULL | 王五   | 语文   |  NULL |
|   NULL | 王五   | 英语   |  NULL |
|   NULL | 王五   | 数学   |    89 |
+--------+--------+--------+-------+
9 rows in set (Elapsed: 00:00:00.01)

gbase> select * from sc_2 PIVOT(sum(sno),sum(score)  S for cname in ('语文' ,'数学'));  
+--------+--------+--------+----------+----------+
| sname  | 语文   | 数学   | 语文_S   | 数学_S   |
+--------+--------+--------+----------+----------+
| 张三   | 202501 | 202503 |       90 |       85 |
| 李四   | 202502 | 202502 |       90 |       91 |
| 王五   |   NULL |   NULL |     NULL |       89 |
+--------+--------+--------+----------+----------+
3 rows in set (Elapsed: 00:00:00.05)