如何在枢轴中应用条件聚合函数? [英] How to apply conditional aggregate function in a pivot?

问题描述

我有以下查询:

SELECT "CASE_RECORD_ID",
  "OPEN_DT",
  "PENDING_DT",
  "CLOSED_DT"
FROM
  (SELECT case_record_id,
    workflow_status_cd,
    workflow_status_dt
  FROM case_workflow_status
  ) PIVOT ( MIN(workflow_status_dt) FOR workflow_status_cd IN ('O' AS open_dt, 'P' AS pending_dt, 'C' AS closed_dt) );

如何根据workflow_status_cd 应用不同的聚合函数?我的非工作尝试如下:

How can I apply a different aggregate function depending on what workflow_status_cd is? My non-working attempt is below:

SELECT "CASE_RECORD_ID",
    "OPEN_DT",
    "PENDING_DT",
    "CLOSED_DT"
  FROM
    (SELECT case_record_id,
      workflow_status_cd,
      workflow_status_dt
    FROM case_workflow_status
    ) 

    PIVOT (

    (
    CASE workflow_status_cd
        WHEN 'O' THEN MIN(workflow_status_dt)
        WHEN 'P' THEN MAX(workflow_status_dt)
        WHEN 'C' THEN MAX(workflow_status_dt)
    END
    )
      FOR workflow_status_cd IN ('O' AS open_dt, 'P' AS pending_dt, 'C' AS closed_dt) 
  );

推荐答案

只需使用适当的聚合函数，并在每个函数中使用 CASE 语句来匹配适当的状态:

Just use the appropriate aggregation functions with a CASE statement in each to match the appropriate status:

SELECT CASE_RECORD_ID,
       MIN( CASE WHEN workflow_status_cd = 'O' THEN workflow_status_dt END ) AS OPEN_DT,
       MAX( CASE WHEN workflow_status_cd = 'P' THEN workflow_status_dt END ) AS PENDING_DT,
       MAX( CASE WHEN workflow_status_cd = 'C' THEN workflow_status_dt END ) AS CLOSED_DT
FROM   case_workflow_status
GROUP BY CASE_RECORD_ID;

这篇关于如何在枢轴中应用条件聚合函数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！