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

【问题】

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: