概述
行列转换是在数据分析中经常用到的一项功能，金仓数据库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 end) as apple,
test-# sum(case fruitname when ‘orange’ then quantity end) as orange,
test-# sum(case fruitname when ‘grape’ then quantity end) as grape,
test-# sum(case fruitname when ‘banana’ then quantity end) as banana
test-# from pivot_t1 group by month order by 1;
month | apple | orange | grape | banana
-------±----------±-----------±---------±-------
1 | 1000 | 1500 | 1800 | 1600
2 | 2000 | 2500 | 2800 | 2600
3 | 3000 | 3500 | 3800 | 3600
4 | 4000 | 4500 | 4800 | 4600
(4 rows)
CROSSTAB语法
crosstab() 函数由 tablefunc扩展包提供。
安装扩展 create extension tablefunc;
test=# \df crosstab
函数列表
架构模式 | 名称 | 结果数据类型 | 参数数据类型 | 类型
----------±---------±-------------±--------------±-----
public | crosstab | SETOF record | text | 函数
public | crosstab | SETOF record | text, integer | 函数
public | crosstab | SETOF record | text, text | 函数
函数说明：
crosstab ( sql text ) ：生成一个“数据透视表”，其中包含行名称和 N 列值，其中 N 由调用查询中指定的行类型决定。
crosstab ( source_sql text, category_sql text ) ：产生一个“数据透视表”，其值列由第二个查询指定。
crosstab ( sql text, N integer ) ：crosstab(text)的废弃版本。
test=# SELECT *
test-# FROM crosstab(
test(# ‘select month,fruitname,quantity
test’# from pivot_t1 order by 1,2’,‘select distinct fruitname from pivot_t1 order by 1’)
test-# AS (month int, apple varchar, banana varchar, grape varchar, orange varchar);
month | apple | banana | grape | orange
-------±------±-------±------±-------
1 | 1000 | 1600 | 1800 | 1500
2 | 2000 | 2600 | 2800 | 2500
3 | 3000 | 3600 | 3800 | 3500
4 | 4000 | 4600 | 4800 | 4500
crosstab() 关键点：
第一个参数，带有按X，Y汇总的SQL子句，返回X，Y，Value格式的数据集；
第二个参数，SQL子句，返回用于水平表头中透视内容的所有值；
使用AS子句明确指定返回的每一个字段名称和类型，子句中列名需要与第二个参数order by结果一一对应。
pivot 语法：
test3=# select * from (select month,fruitname,quantity from pivot_t1)
test3-# pivot(sum(quantity) for fruitname in (‘banana’,‘apple’ ,‘orange’,‘grape’));
month | banana | apple | orange | grape
-------±-------±------±-------±------
1 | 1600 | 1000 | 1500 | 1800
2 | 2600 | 2000 | 2500 | 2800
3 | 3600 | 3000 | 3500 | 3800
4 | 4600 | 4000 | 4500 | 4800
(4 行记录)
pivot 计算指定的聚合值（ sum(quantity) ），但是pivot 不包含显示的group by子句，pivot 隐式group by 是基于所有没在pivot子句中引用的列（month），以及在pivot in子句中指定的一组值。
列转行（unpivot）
构造数据：
create table unpivot_t1(fruitname text,q1 integer,q2 integer,q3 integer,q4 integer);
insert into unpivot_t1 values(‘apple’, 1100,1200,1300,1400);
insert into unpivot_t1 values(‘orange’,2100,2200,2300,null);
insert into unpivot_t1 values(‘grape’, 3100,null,3300,3400);
insert into unpivot_t1 values(‘banana’,4100,4200,4300,4400);
1.union all 语法
test=# select fruitname,‘q1’,q1 from unpivot_t1
test-# union all
test-# select fruitname,‘q2’,q2 from unpivot_t1
test-# union all
test-# select fruitname,‘q3’,q3 from unpivot_t1
test-# union all
test-# select fruitname,‘q4’,q4 from unpivot_t1;
fruitname | ?COLUMN? | q1
-----------±---------±-----
apple | q1 | 1100
orange | q1 | 2100
grape | q1 | 3100
banana | q1 | 4100
apple | q2 | 1200
orange | q2 | 2200
grape | q2 |
banana | q2 | 4200
apple | q3 | 1300
orange | q3 | 2300
grape | q3 | 3300
banana | q3 | 4300
apple | q4 | 1400
orange | q4 |
grape | q4 | 3400
banana | q4 | 4400
(16 rows)
unnest 函数
Unnest：将一个数组分解成一组行。
test=# select fruitname,unnest(array[‘q1’,‘q2’,‘q3’,‘q4’]),unnest(array[q1,q2,q3,q4]) from unpivot_t1;
fruitname | unnest | unnest
-----------±-------±-------
apple | q1 | 1100
apple | q2 | 1200
apple | q3 | 1300
apple | q4 | 1400
orange | q1 | 2100
orange | q2 | 2200
orange | q3 | 2300
orange | q4 |
grape | q1 | 3100
grape | q2 |
grape | q3 | 3300
grape | q4 | 3400
banana | q1 | 4100
banana | q2 | 4200
banana | q3 | 4300
banana | q4 | 4400
unpivot 语法
test=# select fruitname,month,quantity from unpivot_t1 unpivot include nulls
test-# (quantity for month in (q1 as ‘Q1’,q2 as ‘Q2’,q3 as ‘Q3’,q4 as ‘Q4’)) order by fruitname,month;
fruitname | month | quantity
-----------±------±---------
apple | Q1 | 1100
apple | Q2 | 1200
apple | Q3 | 1300
apple | Q4 | 1400
banana | Q1 | 4100
banana | Q2 | 4200
banana | Q3 | 4300
banana | Q4 | 4400
grape | Q1 | 3100
grape | Q2 |
grape | Q3 | 3300
grape | Q4 | 3400
orange | Q1 | 2100
orange | Q2 | 2200
orange | Q3 | 2300
orange | Q4 |
(16 行记录)
参考文档：
《SQL语言》
