在这篇文章中，我们来一起看看 南大通用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.00
clerk 20 3000.00
manager 10 2450.00
manager 20 5825.00
president 10 5000.00
salesman 10 1500.00
salesman 20 1250.00
salesman 30 2850.00
8 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_clause
IN (10, 20,30,40) --pivot_in_cluase
) ;
Result:
job 10 20 30 40
president 5000.00
manager 2450.00 5825.00
salesman 1500.00 1250.00 2850.00
clerk 800.00 3000.00
4 row(s) retrieved.关于这个例子需要注意的地方：
pivot 操作实际执行了隐式的 GROUP BY，使用的列是没有出现在 pivot_clause 中的列（本例为 job和 deptno），大多数的转换查询都是在列的特定的子集上来执行。像所有的聚合查询一样，额外列的出现会影响分组。在本例中，除了 sal 1列，其余所有列都成了分组集，其中 deptno 为转换列。基础数据中的其他列如ename等如果加入就会影响分组，所以可以使用子查询等来定义基列的集合，例子中的SELECT deptno, job, sal1 FROM emp作用就是定义基列的集合。
--用例2：所有列都参与的效果：
SELECT * FROM emp
PIVOT (
SUM(sal1)
FOR deptno
IN (10, 20, 30)
);
Result:
empno 2
ename allen
job salesman
sal2 5491.00
10
20
30 1600.00
empno 7
ename clark
job manager
sal2 1000.00
10 2450.00
20
30
empno 3
ename ward
job salesman
sal2 3429.00
10
20
30 1250.00
empno 8
ename scott
job clerk
sal2 2000.00
10
20 3000.00
30
empno 1
ename smith
job clerk
sal2 910.00
10 800.00
20
30
empno 9
ename king
job president
sal2 4500.00
10 5000.00
20
30
empno 5
ename martin
job salesman
sal2 1256.34
10
20 1250.00
30
empno 4
ename jones
job manager
sal2 1298.00
10
20 2975.00
30
empno 10
ename turner
job salesman
sal2 1000.00
10 1500.00
20
30
empno 6
ename blake
job manager
sal2 1234.34
10
20 2850.00
30
10 row(s) retrieved.本例中，除了 sal1 列，其余所有列都成了分组集，其中 deptno 为转换列。这个转换没有什么意义。
--用例3：with as
下面再举一个with as子查询的例子，效果与上述内联视图结果一致。
WITH pivot_data AS (SELECT deptno, job, sal1 FROM emp)
SELECT * FROM PIVOT_data
PIVOT
(
SUM(sal1)
FOR deptno
IN (10, 20, 30,40)
);
Result:
job 10 20 30 40
president 5000.00
manager 2450.00 5825.00
salesman 1500.00 1250.00 2850.00
clerk 800.00 3000.00
4 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 deptno
IN (10,20)
);
Result:
job 10_sum_sal1 20_sum_sal1 10_sum_sal2 20_sum_sal2
president 5000.00 4500.00
manager 2450.00 5825.00 1000.00 2532.34
salesman 1500.00 1250.00 1000.00 1256.34
clerk 800.00 3000.00 910.00 2000.00
4 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_sal2
FOR (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.34
1 row(s) retrieved.GBase 8s Pivot转换查询注意事项
在 pivot_for_clause 中使用的列（如例子中为 deptno 列)不能出现在SELECT投影列中，在 pivot_for_clause 中的列会根据我们在 pivot_in_clause中提供的值的范围进行分组。下列中试图查询 deptno 列，会引发异常，因为这种情况下，deptno 完全从投影中被移除了。
> SELECT deptno
FROM emp
PIVOT
(
SUM(sal1)
FOR deptno
IN(10, 20, 30, 40)
);
217: Column (deptno) not found in any table in the query (or SLV is undefined).
Error in line 2
Near 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)a
PIVOT(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.34
1 row(s) retrieved.其他使用上的限制，可以参考《GBase 8s V8.8 SQL 指南：语法.pdf》
GBase 8s数据库官方文档：官方文档
通过本文的介绍，您可能已经对GBase 8s中的Pivot功能有了全面的认识。它不仅仅是一个简单的数据转换工具，更是数据分析的利器。下一篇文章我们会来说说 Unpivot 的那些事儿，感谢您的阅读。
