概述
行列转换是在数据分析中经常用到的一项功能,金仓数据库KingbaseES从V8R6C3B0071版本开始通过扩展插件(kdb_utils_function)支持pivot和unpivot功能。在之前的版本如果需要进行行列转换操作要如何处理呢?
下面介绍pivot和unpivot通用的写法,并给出了pivot和unpivot 用法。
行转列(pivot)
构造数据:
create table pivot_t1(month integer,fruitname text,quantity integer);
insert into pivot_t1 values(1,‘apple’,1000);
insert into pivot_t1 values(2,‘apple’,2000);
insert into pivot_t1 values(3,‘apple’,3000);
insert into pivot_t1 values(4,‘apple’,4000);
insert into pivot_t1 values(1,‘orange’,1500);
insert into pivot_t1 values(2,‘orange’,2500);
insert into pivot_t1 values(3,‘orange’,3500);
insert into pivot_t1 values(4,‘orange’,4500);
insert into pivot_t1 values(1,‘grape’,1800);
insert into pivot_t1 values(2,‘grape’,2800);
insert into pivot_t1 values(3,‘grape’,3800);
insert into pivot_t1 values(4,‘grape’,4800);
insert into pivot_t1 values(1,‘banana’,1600);
insert into pivot_t1 values(2,‘banana’,2600);
insert into pivot_t1 values(3,‘banana’,3600);
insert into pivot_t1 values(4,‘banana’,4600);
-
case when语法
test=# select month,
test-# sum(case fruitname when ‘apple’ then quantity en