SELECT: PIVOT¶
说明¶
将查询结果由行转列
参数¶
aggregate_function
这部分定义了要汇总的列，作为 pivot_in_clause 新增列的值。只能是聚集函数，不能是函数表达式。现在后台支持简单的聚集函数包括：MAX、 MIN、 AVG、 COUNT、 SUM、 VARIANCE、 STDDEV、 MEDIAN、 LISTAGG
expr
列名或表达式，作为聚集函数的参数
alias
作为聚集函数的别名或者 pivot_in_clause 新增列的别名
pivot_for_clause
定义要分组和转置的列， column 列必须来自于原表
pivot_in_clause
定义了 pivot_for_clause 中的列的限制范围。对于 pivot_in_clause 中的每个值，聚合计算将被转置为单独的一列。作为转换后新增的列名，且列名需要根据别名来定，别名的定义如下:
pivot_in_clause别名 pivot_aggfunc_list别名 转换后新增列名描述 列名 例子 N N 1)是in里面的元素值
2)是in里面的元素值通过下划线连接
1)'Chinese','Math','English'三列
2)'Chinese_n1' ,'Math_n2' ,'English_n3' 三列
1)pivot(sum(score)for course in('Chinese','Math','English'))
2)pivot(sum(score) for(course,teacher)in (('Chinese','n1') ,('Math','n2'),('English','n3')))
Y Y 新增列名就是in元素的别名下划线连接pivot_aggfunc_list的别名 1)C_S ,M_S ,E_S 三列
2)C_S ,T1_S ,M_S ,T2_S ,E_S ,T3_S六列
1)pivot(sum(score)S for course in('Chinese' as 'C','Math' as 'M','English' as 'E'))
2)pivot(sum(score)S for(course,teacher)in (('Chinese','n1')as ('C','T1') ,('Math','n2') as('M','T2'),('English','n3')as ('E','T3')))
N Y 1)是in里面的元素下划线连接pivot_aggfunc_list的别名
2)是in里面的元素值通过下划线连接再下划线连接pivot_aggfunc_list的别名
1)Chinese_S, Math_S, English_S三列
2)'Chinese_n1_S', 'Math_n2_S', 'English_n3_S' 三列
1)pivot(sum(score)s for course in('Chinese','Math','English'))
2)pivot(sum(score) s for(course,teacher)in (('Chinese','n1') ,('Math','n2'),('English','n3')))
Y N 新增列名就是in元素的别名 1)C, M, E三列
2)C, T1, M, T2, E, T3六列
1)pivot(sum(score) for course in('Chinese' as 'C','Math' as 'M','English' as 'E'))
2)pivot(sum(score) for(course,teacher)in (('Chinese','n1')as ('C','T1') ,('Math','n2') as('M','T2'),('English','n3')as ('E','T3')))
注解
1)pivot 只对前面临近的结果集进行行转列，结果集可以是一个表，一个子查询，或者函数。可参考 select 用法里的 table_ref ，且如果是函数的情况，只能是 table() 函数。
2)aggregate_function 只能是聚集函数，不能是函数表达式。
3)每个部分在有别名的情况下都需要保证最终转换的列不能有歧义。
4)pivot_in_clause 链表只能是常量或常量表达式， 可以是单链表，或者多重链表如：
in('Chinese'"C" , 'Math'"M",'English'"E"))或者 in (('Chinese','n1') s_t120691,('Math','n2')s_t2,('English','n3')s_t3)) 其中是单链表和多重链表取决于 pivot_for_clause 的长度，即 pivot_in_clause 和 pivot_for_clause 链表长度保持一致，一一对应
示例¶
示例1: 聚集函数aggregate_function测试
-- 清理环境
drop table t;
create table t(id int ,name varchar(100) null,course varchar(100) null,score int,teacher varchar(100),teachtime int);
insert into t values(11,'zs','Chinese',40,'n1',35);
insert into t values(11,'zs','Math',80,'n2',50);
insert into t values(11,'zs','English',60,'n3',80);
insert into t values(12,'ls','Chinese',60,'n1',50);
insert into t values(12,'ls', 'Math',100,'n2',20);
insert into t values(12,'ls', 'English',50,'n3',50);
insert into t values(11,'zs', 'Chinese',140,'n1',30);
select * from t pivot(sum(score)for course in('Chinese','Math','English'))order by id,name,teacher,teachtime;
ID(int) |NAME(varchar) |TEACHER(varchar) |TEACHTIME(int) |'Chinese'(bigint) |'Math'(bigint) |'English'(bigint) |
--------------------------------------------------------------------------------------------------------------------------------------------
11 |zs |n1 |30 |140 |null |null |
--------------------------------------------------------------------------------------------------------------------------------------------
11 |zs |n1 |35 |40 |null |null |
--------------------------------------------------------------------------------------------------------------------------------------------
11 |zs |n2 |50 |null |80 |null |
--------------------------------------------------------------------------------------------------------------------------------------------
11 |zs |n3 |80 |null |null |60 |
--------------------------------------------------------------------------------------------------------------------------------------------
12 |ls |n1 |50 |60 |null |null |
--------------------------------------------------------------------------------------------------------------------------------------------
12 |ls |n2 |20 |null |100 |null |
--------------------------------------------------------------------------------------------------------------------------------------------
12 |ls |n3 |50 |null |null |50 |
总数目：7
WARNING, 在计算集合函数过程中有空值被忽略
示例2: 转置列pivot_for_clause测试
select * from t pivot(sum(score) sum_s,sum(teachtime)sum_t for(course,teacher)in (('Chinese','n1') s_t,('Math','n2')s_t2,('English','n3')s_t3)) order by id;
ID(int) |NAME(varchar) |S_T_SUM_S(bigint) |S_T_SUM_T(bigint) |S_T2_SUM_S(bigint) |S_T2_SUM_T(bigint) |S_T3_SUM_S(bigint) |S_T3_SUM_T(bigint) |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
11 |zs |180 |65 |80 |50 |60 |80 |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
12 |ls |60 |50 |100 |20 |50 |50 |
总数目：2
WARNING, 在计算集合函数过程中有空值被忽略
示例3: pivot_in_clause测试
select * from t pivot(sum(score) for course in('Chinese'"C" , 'Math'"M",'English'"E")) order by id,name,teacher,teachtime;
ID(int) |NAME(varchar) |TEACHER(varchar) |TEACHTIME(int) |C(bigint) |M(bigint) |E(bigint) |
-----------------------------------------------------------------------------------------------------------------------
11 |zs |n1 |30 |140 |null |null |
-----------------------------------------------------------------------------------------------------------------------
11 |zs |n1 |35 |40 |null |null |
-----------------------------------------------------------------------------------------------------------------------
11 |zs |n2 |50 |null |80 |null |
-----------------------------------------------------------------------------------------------------------------------
11 |zs |n3 |80 |null |null |60 |
-----------------------------------------------------------------------------------------------------------------------
12 |ls |n1 |50 |60 |null |null |
-----------------------------------------------------------------------------------------------------------------------
12 |ls |n2 |20 |null |100 |null |
-----------------------------------------------------------------------------------------------------------------------
12 |ls |n3 |50 |null |null |50 |
总数目：7
WARNING, 在计算集合函数过程中有空值被忽略
select * from t pivot (sum(score) for id in (9, 12, (11+1+1)))order by name,teacher,teachtime;
NAME(varchar) |COURSE(varchar) |TEACHER(varchar) |TEACHTIME(int) |9(bigint) |12(bigint) |(11+1+1)(bigint) |
---------------------------------------------------------------------------------------------------------------------------------------
ls |Chinese |n1 |50 |null |60 |null |
---------------------------------------------------------------------------------------------------------------------------------------
ls |Math |n2 |20 |null |100 |null |
---------------------------------------------------------------------------------------------------------------------------------------
ls |English |n3 |50 |null |50 |null |
---------------------------------------------------------------------------------------------------------------------------------------
zs |Chinese |n1 |30 |null |null |null |
---------------------------------------------------------------------------------------------------------------------------------------
zs |Chinese |n1 |35 |null |null |null |
---------------------------------------------------------------------------------------------------------------------------------------
zs |Math |n2 |50 |null |null |null |
---------------------------------------------------------------------------------------------------------------------------------------
zs |English |n3 |80 |null |null |null |
总数目：7
WARNING, 在计算集合函数过程中有空值被忽略
示例4: from结果集是函数测试
-- 清理环境
drop function func;
drop type type1;
drop function f2;
create type type1 is table of int;
/
create function func return type1
is
a type1 := type1(1,2,3);
begin
return a;
end;
/
select * from table(func) pivot(sum(column_value) for column_value in(1,2,3)) t;
1(bigint) |2(bigint) |3(bigint) |
---------------------------------------------
1 |2 |3 |
总数目：1
WARNING, 在计算集合函数过程中有空值被忽略
-- 删除函数
drop function func;
drop type type1;
drop table t;