在这篇文章中，我们来一起看看 南大通用GBase 8s数据库中的Pivot函数——将行数据转换为列数据的功能。无论是报表制作还是数据分析，Pivot功能都能帮助我们从不同的角度审视数据。

GBase 8s Pivot函数的概述

Pivot，即行转列，是一种将表中的行数据转换为列数据的过程。通过Pivot操作，我们可以将数据表中的一列或多列的数据转化为多列的形式，以便更好地进行横向统计和分析。

Unpivot，即列转行，与Pivot相反，Unpivot则是将表中的列数据转换为行数据。以便更好地进行纵向统计和分析。

Pivot和Unpivot是数据转换中非常有用的两个函数，它们可以根据需要改变数据的呈现方式，使得数据更易于理解和分析。在数据库查询、数据分析、报表制作等领域中，都有着广泛的应用。我们将在下一篇文章介绍Unpivot函数。

GBase 8s pivot的语法详解

Pivot 转换数据的过程：多行被聚合，然后转换成列，每一列表示聚合数据的不同范围。语法如下：

SELECT ...         
FROM ...   
PIVOT
( 
pivot_clause      ---行转列后的列的值，为聚合值，支持多个
pivot_for_clause ---需要转换为列的字段，pivot函数将一列数据转换为多列数据，
因此需要明确哪些列数据需要进行转换
  pivot_in_clause  ---pivot_for_clause 子句中列的过滤器，简单来说就是需要转换为列的字段值
)   
WHERE ...

pivot紧跟from，位于from和where之间。

【 一句话来说明 】：pivot(聚合函数1 as 别名1，聚合函数2 as 别名2 ...) for 需要转为列的字段1,字段2.... in((字段1值1，字段2值1) as 别名1,(字段1值2，字段2值2) as 别名2...)

GBase 8s Pivot示例

注意 ：以下所用示例使用的数据库版本为:GBase8sV8.8_TL_3.5.1_x86_64。

下面的例子都是对 pivot 语法的简单的演示，使用 emp 表，在转换前，先看一下基础数据：

create table emp(empno int,ename varchar(10),job varchar(9),sal1 DECIMAL(10,2),sal2 DECIMAL(10,2), deptno int);
-- 插入10条数据：
INSERT INTO emp  VALUES (1, 'smith', 'clerk', 800.00,910,10);
INSERT INTO emp  VALUES (2, 'allen', 'salesman', 1600.00,5491,30);
INSERT INTO emp  VALUES (3, 'ward', 'salesman', 1250.00,3429,30);
INSERT INTO emp  VALUES (4, 'jones', 'manager', 2975.00,1298,20);
INSERT INTO emp  VALUES (5, 'martin', 'salesman',1250.00,1256.34,20);
INSERT INTO emp  VALUES (6, 'blake', 'manager', 2850.00,1234.34,20);
INSERT INTO emp  VALUES (7, 'clark', 'manager', 2450.00,1000,10);
INSERT INTO emp  VALUES (8, 'scott', 'clerk', 3000.00,2000,20);
INSERT INTO emp  VALUES (9, 'king', 'president',5000.00,4500,10);
INSERT INTO emp  VALUES (10, 'turner', 'salesman', 1500.00,1000,10);
> select job,deptno,sum(sal1) as sum_sal1 from emp group by job,deptno order by job,deptno;
job            deptno         sum_sal1
clerk              10           800.00clerk              20          3000.00manager            10          2450.00manager            20          5825.00president          10          5000.00salesman           10          1500.00salesman           20          1250.00salesman           30          2850.008 row(s) retrieved.

--用例1：单列转换

这里将通过 deptno和 job 来汇总sal1，然后转换对deptno的汇总到它们各自的列上

需要聚合的列：sal1

需要转换的列：deptno

转换的列需要过滤的值：10,20,30,40

select * from (SELECT deptno,job, sal1 FROM emp)a               
PIVOT(                                                                                 
SUM(sal1) --pivot_clause
FOR deptno --pivot_for_clauseIN (10, 20,30,40) --pivot_in_cluase                                   
) ;
Result:
job                  10               20               30               40president           5000.00manager             2450.00          5825.00salesman            1500.00          1250.00          2850.00clerk               800.00          3000.004 row(s) retrieved.

关于这个例子需要注意的地方：

pivot 操作实际执行了隐式的 GROUP BY，使用的列是没有出现在 pivot_clause 中的列（本例为 job和 deptno），大多数的转换查询都是在列的特定的子集上来执行。像所有的聚合查询一样，额外列的出现会影响分组。在本例中，除了 sal 1列，其余所有列都成了分组集，其中 deptno 为转换列。基础数据中的其他列如ename等如果加入就会影响分组，所以可以使用子查询等来定义基列的集合，例子中的SELECT deptno, job, sal1 FROM emp作用就是定义基列的集合。

--用例2：所有列都参与的效果：

SELECT * FROM empPIVOT (
SUM(sal1)FOR deptnoIN (10, 20, 30)
 );
Result:
empno  2ename  allen
job    salesman
sal2   5491.0010     20     30     1600.00empno  7ename  clark
job    manager
sal2   1000.0010     2450.0020     30     empno  3ename  ward
job    salesman
sal2   3429.0010     20     30     1250.00empno  8ename  scott
job    clerk
sal2   2000.0010     20     3000.0030     empno  1ename  smith
job    clerk
sal2   910.0010     800.0020     30     empno  9ename  king
job    president
sal2   4500.0010     5000.0020     30     empno  5ename  martin
job    salesman
sal2   1256.3410     20     1250.0030     empno  4ename  jones
job    manager
sal2   1298.0010     20     2975.0030     empno  10ename  turner
job    salesman
sal2   1000.0010     1500.0020     30     empno  6ename  blake
job    manager
sal2   1234.3410     20     2850.0030     10 row(s) retrieved.

本例中，除了 sal1 列，其余所有列都成了分组集，其中 deptno 为转换列。这个转换没有什么意义。

--用例3：with as

下面再举一个with as子查询的例子，效果与上述内联视图结果一致。

WITH pivot_data AS (SELECT deptno, job, sal1 FROM emp)SELECT * FROM PIVOT_dataPIVOT(
SUM(sal1) FOR deptno 
IN (10, 20, 30,40));
Result:
job                10               20             30               40president          5000.00manager          2450.00         5825.00salesman          1500.00         1250.00          2850.00clerk              800.00          3000.004 row(s) retrieved.

--用例4：多列转换

select * from (SELECT deptno,job, sal1,sal2 FROM emp)a 
PIVOT(
SUM(sal1) AS sum_sal1,
SUM(sal2) AS sum_sal2
FOR deptnoIN (10,20));
Result:
job            10_sum_sal1      20_sum_sal1      10_sum_sal2      20_sum_sal2
president         5000.00                         4500.00manager         2450.00         5825.00          1000.00         2532.34salesman         1500.00         1250.00          1000.00         1256.34clerk             800.00          3000.00           910.00          2000.004 row(s) retrieved.

上面的例子中 pivot_clause 中定义 2 个聚合，这里得到的转换列是双倍（因为聚合的数量是双倍）。

转换列的数量 = 聚合列的数量 * pivot_in_clause 中值的个数（2*2=4）。转换列的数量不能超过 1024。

--用例5，多列多值转换

SELECT * FROM (SELECT deptno,job, sal1,sal2 FROM emp)a 
PIVOT(
SUM(sal1) AS sum_sal1,
SUM(sal2) AS sum_sal2FOR (deptno, job)IN ((10, 'clerk') as a,(20, 'manager') as b)
);
Result:
     a_sum_sal1       b_sum_sal1       a_sum_sal2       b_sum_sal2           800.00          5825.00           910.00          2532.341 row(s) retrieved.

GBase 8s Pivot转换查询注意事项

在 pivot_for_clause 中使用的列（如例子中为 deptno 列)不能出现在SELECT投影列中，在 pivot_for_clause 中的列会根据我们在 pivot_in_clause中提供的值的范围进行分组。下列中试图查询 deptno 列，会引发异常，因为这种情况下，deptno 完全从投影中被移除了。

> SELECT deptno
FROM empPIVOT(
SUM(sal1)FOR deptnoIN(10, 20, 30, 40));   217: Column (deptno) not found in any table in the query (or SLV is undefined).
Error in line 2Near character position 0

同样也不能查询任何出现在 pivot_clause 中的列。比如试图查询 sal1 列，也会引发了同样的异常。

pivot_clause 中的列必须使用聚合函数，没用使用聚合函数，也会引发异常。

> SELECT * FROM emp PIVOT(sal1 FOR deptno IN(10, 20, 30, 40));201: A syntax error has occurred.

pivot_in_clause 中，如果指定别名就用别名，如果没有指定就用值当列名；而 pivot_clause中如果指定别名就连接到 pivot_in_clause 转换后的相应列的名称的后面，如果没有指定就不用。pivot_clause中有多个聚合函数时，8s允许都不设置别名，8s会自动在后面加上_1,_2..依次类推。别名都不支持加单引号。

> SELECT * FROM (SELECT deptno,job, sal1,sal2 FROM emp)aPIVOT(SUM(sal1),SUM(sal2)  FOR (deptno, job) IN ((10, 'clerk'),(20, 'manager'))); 
Result：      
10_clerk       20_manager       10_clerk_1     20_manager_1   800.00          5825.00           910.00          2532.341 row(s) retrieved.

其他使用上的限制，可以参考《GBase 8s V8.8 SQL 指南：语法.pdf》

GBase 8s数据库官方文档： 官方文档

通过本文的介绍，您可能已经对GBase 8s中的Pivot功能有了全面的认识。它不仅仅是一个简单的数据转换工具，更是数据分析的利器。下一篇文章我们会来说说 Unpivot 的那些事儿，感谢您的阅读。