不支持 pivot 的数据库转置方案

润乾软件

于 2022-01-07 10:42:01 发布

阅读量197

点赞数

分类专栏： JAVA计算文章标签：数据库 sql

本文链接：https://blog.csdn.net/raqsoft/article/details/122359495

版权

在使用 Jaspersoft Studio 创建报告时，遇到Amazon Redshift不支持pivot功能的问题。文章讨论了如何通过SPL脚本来实现数据转置，以适应JasperReport的交叉表需求，提供了从执行SQL到数据写入的步骤，并提到了JasperReport调用SPL脚本的方法。

摘要由CSDN通过智能技术生成

【问题】

I am currently creating a report (jrxml) using Jaspersoft Studio and I am using the amazon redshift as datasource. I have a sql script that will compute base on a series of data and an example is below:

select date_month_yr, sum(total_count), sum(total_nerwork), (sum(total_count)/lag(sum(total_count)) - 1)*100 as per_total_count_change from ( select date_month_yr, total_count, total_count from tb where …. ….. union all select date_month_yr, total_count, total_count from tb where ….)

The result would be:

date\_month\_yr|sum(total\_count)|sum(total\_nerwork)| per\_total\_count_change

2015-05_ _ _ _|1234_ _ _ _ _ _ _ _ |4321_ _ _ _ _ _ _ _ _|

2015-06_ _ _ _|2345_ _ _ _ _ _ _ _ |8642_ _ _ _ _ _ _ _ _|90.03

2015-07_ _ _ _|3456_ _ _ _ _ _ _ _ |9876_ _ _ _ _ _ _ _ _|47.37

......

The above result is not possible to use as data for the crosstab of jasper that should generate similar to: