VastbaseG100

兼容pivot

功能描述

pivot为select语句的子语句允许将行旋转为列的交叉表查询，在旋转过程中聚合函数、数据透视(pivot data）是整个操作的关键。数据透视时，会对数据透视列值列表中的每个项应用聚合运算符。数据透视列不能包含任意表达式。

语法格式

1.pivot语法

SELECT …
FROM <table-expr>
PIVOT
(
aggregate-function(<column>) AS <alias>
FOR <pivot-column> IN (<value1>,<values2>,…,<valuen>)
) AS <alias>
WHERE …

2.pivot子句划分

SELECT …
FROM …
PIVOT[XML]
(pivot_clause
pivot_for_clause
pivot_in_clause) 
WHERE …

说明

• pivot_clause

  定义需要聚合的列（Pivot是一个聚合操作），即上述语法“aggregate-function() AS ”部分聚合函数。聚合函数必须指定group by子句，但pivot_clause不包含显式group by子句。相反，pivot_clause执行隐式group by。隐式分组基于pivot_clause和 pivot_for_clause中未引用的所有列。

• pivot_for_clause

  定义要分组和透视的列。即上述语法“FOR ”部分。

• pivot_in_clause

  定义pivot_for_clause中列的过滤器（即限制结果的值范围）。pivot_in_clause中每个值的聚合将被转置到单独的列中，即上述语法“IN (,,..,)”部分

  pivot_in_clause子句中与xml结合使用subquery以及any。

注意

• 暂不支持将查询结果输出为xml格式。

示例

create table tmp2 (student varchar2(20), course varchar2(20), score number, class number);
insert into tmp2 values('张三','语文', 78, 1),('张三','数学',87, 2),('张三','数学',90, 1),('张三','数学',60, 2),('张三','英语',82, 3),('张三','英语',88, 1),('张三','英语',89, 2),('张三','物理',90, 2),('张三','天文',90, 1),('李四','地理',90, 2),('李四','语文',65, 2),('李四','语文',95, 1),('李四','语文',65, 3),('李四','数学',77, 1),('李四','英语',65, 2),('李四','英语',85, 2),('李四','英语',95, 2),('李四','物理',85, 3),('张三','Physics',100, 2),('张三','Math',70, 1),('李四','Physics',60, 2),('李四','Chinese',65, 2);
select *  from tmp2 pivot (sum(score) for course in ('语文' as c , '数学' as m, '英语' as e,'物理' as p, '天文', '地理')) order by class;