使用java的MySQL数据透视表 [英] MySQL pivot table using java

问题描述

我有一张表 BPFinal 并且有以下列

I have one table BPFinal and has the following column

ID   |  Partners  | Branch | Amount | Date 
1001 |  ABC       | BO1    | 2,000  | 2020/11/30
1001 |  ABC       | BO2    | 1,500  | 2020/11/30
1002 |  XYZ       | BO1    | 4,000  | 2020/11/30
1001 |  ABC       | BO1    | 5,000  | 2020/10/31

我正在尝试编写 sql 来创建一个带有合作伙伴动态标题的数据透视表.设置日期后，它将只显示可用的合作伙伴及其每个分支的相应数据.输出应该是这样的:

I am trying to write sql to create a Pivot Table with Dynamic Headers of Partners. Once date is set, it will only display the available partners and its corresponding data per branch. Output should be like this:

日期:2020/11/30

Date : 2020/11/30

Branches | ABC   | XYZ
BO1      | 2,000 | 4,000
BO2      | 1,500 | 0.00

日期:2020/10/31

Date: 2020/10/31

Branches | ABC
BO1      | 5,000

在编写 SQL 方面的任何帮助将不胜感激.谢谢

Any help in writing the SQL would be appreciated. Thanks

推荐答案

您可以使用动态 SQL 来动态地进行透视，例如

You can use dynamic SQL in order to pivot dynamically such as

SET @sql = NULL;
SET @date = '2020-11-30';

SELECT GROUP_CONCAT(
             CONCAT(
                    'SUM(CASE WHEN Partners = "', Partners,'" THEN Amount ELSE 0 END ) AS'
                    ,Partners
                    )
       )
  INTO @sql
  FROM ( SELECT DISTINCT Partners FROM BPFinal WHERE Date = @date ) AS b;

SET @sql = CONCAT('SELECT Branch,',@sql,
                   ' FROM BPFinal
                    WHERE Date = "',@date,'"' 
                  ' GROUP BY Branch'); 
                  
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt; 

演示

这篇关于使用java的MySQL数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！