lightdb 23.4 支持pivot行转列

前言

Lightdb-x支持行转列、列转行功能：
pivot支持的语法如下：

pivot支持项测试

pivot测试

create table hs_pivot(name varchar(40),course varchar(100),score int);
insert into hs_pivot values(‘zhangsan’,‘chinese’,90);
insert into hs_pivot values(‘zhangsan’,‘math’,100);
insert into hs_pivot values(‘lisi’,‘chinese’,90);
insert into hs_pivot values(‘lisi’,‘math’,88);

select * from hs_pivot pivot (sum(score) for course in(‘chinese’,‘math’));
name | ‘chinese’ | ‘math’
----------±----------±-------
lisi | 90 | 88
zhangsan | 90 | 100
(2 rows)

select * from hs_pivot pivot (sum(score) for course in(‘chinese’ as er,‘math’ as wr));
name | er | wr
----------±—±----
lisi | 90 | 88
zhangsan | 90 | 100
(2 rows)

select * from hs_pivot pivot (sum(score) for course in(‘chinese’ as a, ‘math’ as b )) c where c.a =90;
name | a | b
----------±—±----
zhangsan | 90 | 100
lisi | 90 | 88
(2 rows)

不支持情况

Pivot不支持项：

1.具有ANY的XML不支持
添加XML关键字，无法在IN子句中指定值，我们将需要使用子查询或使用关键字ANY
2.执行多个聚集函数不支持
PIVOT (
SUM(sale_amount),
COUNT(sale_amount)
FOR customer_id
IN (1, 2, 3, 4)
);

PIVOT (
SUM(sale_amount) AS sum_sales,
COUNT(sale_amount) AS count_sales
FOR customer_id
IN (1, 2, 3, 4)
);
3.按多列分组不支持
PIVOT (
SUM(sale_amount)
FOR (customer_id, prod_category)
IN (
(1, ‘furniture’) AS furn1,
(2, ‘furniture’) AS furn2,
(1, ‘electronics’) AS elec1,
(2, ‘electronics’) AS elec2
)
4.不支持in(())：
select * from test123
PIVOT (
SUM(score) as tc
FOR course
IN ());
5.不支持 ‘(’ select_with_parens opt_alias_clause opt_conversion_clause ‘)’ opt_alias_clause
6.as别名为小写，这部分需要兼容oracle大写的逻辑进行处理。
select * from hs_pivot pivot (sum(score) for course in(‘chinese’ as er,‘math’ as wr));
7.函数的参数只支持字段，不支持数字和*。