从Access到DB2的转换语句

Question

我有以下问题，我正在尝试理解和转换它转换为db2格式：

TRANSFORM Sum(Cases) AS SumOfCases
SELECT Process, Sum(Cases) AS total
FROM tbl
GROUP BY Process
PIVOT tbl.STATUS;

表中的数据如下：

流程状态案例
a已取消14
a已关闭179
b取消20
b关闭30
b等待10

如何将该查询写入db2？我尝试了以下查询：

SELECT Process
, MAX(CASE WHEN STATUS = 'Cancelled' THEN CASES END) "Cancelled"
, MAX(CASE WHEN STATUS = 'Closed' THEN CASES END) "Closed"
, MAX(CASE WHEN STATUS = 'Pending' THEN CASES END) "Pending"
FROM tbl
GROUP BY Process;

由于我没有MS Access，因此我不确定我是否在db2中做了正确的事情。

如果我能在这方面得到一些建议，我将不胜感激。

Answer 1

您的DB2查询在复制Access的交叉表查询时可以正常工作，只是错过了Total列。顺便说一下，任何聚合函数都适用于CASE/WHEN语句：MIN()、MAX()、MEDIAN()、AVG()，甚至SUM():

SELECT Process
, SUM(CASES) AS "Total"
, MAX(CASE WHEN STATUS = 'Cancelled' THEN CASES END) AS "Cancelled"
, MAX(CASE WHEN STATUS = 'Closed' THEN CASES END) AS "Closed"
, MAX(CASE WHEN STATUS = 'Pending' THEN CASES END) AS "Pending"
FROM tbl
GROUP BY Process;