关键字：    行专列，列转行, pivot, unpivot

行列转换是在数据分析中经常用到的一项功能，KingbaseES从V8R6C3B0071版本开始通过扩展插件(kdb_utils_function)支持了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);

1. 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)

2. 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结果一一对应。

3. 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)

　　

2. 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

　　

3. 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语言

KingbaseES 行列转换函数的相关教程结束。