SQL Server中的聚合和分组问题

I'm creating a query that groups status dates of member records on one line in the following format:

我正在创建一个查询，按以下格式将成员记录的状态日期分为一行：

     MemberID  P1StatusDate   P2StatusDate   P3StatusDate   P4StatusDate
      10        NULL             NULL         NULL           08/20/2017

In this particular case the first 3 statuses are null, but they can be populated. I created this with the following query:

在这种特殊情况下，前3个状态为空，但可以填充它们。我用以下查询创建了这个：

    select memberid, max(case when statustype = 'Applicant' then cast(B.statusdate as date)end) as P1StatusDate
    max(case when B.statustype = 'Pending' then cast(B.statusdate as date)end) as P2StatusDate
    max(case when B.statustype = 'In Progess' then cast(sh.statusdate as date)end) as P3StatusDate
    max(case when B.statustype = 'Approved' then cast(B.statusdate as date)end) as P4StatusDate
    from Table A
    inner join B
    on A.statusdate = B.statusdate
    group by memberid

The problem I've found is that if a member has 2 Approved records with different dates, only one shows up as P4StatusDate. For example:

我发现的问题是，如果一个成员有2个具有不同日期的已批准记录，则只有一个显示为P4StatusDate。例如：

    MemberID    Status    StatusDate
     10          Approved    08/19/2017
     10          Approved    08/20/2017

This appears as:

这看起来像：

     MemberID    P1StatusDate  P2StatusDate   P3StatusDate   P4StatusDate
      10          Null          Null             Null          08/20/2017

The 08/19/2017 record is omitted.

省略了08/19/2017的记录。

If I alter the query to include the statusdate in the group by, it will correctly populate both records for this member.

如果我更改查询以在组中包含statusdate，它将正确填充此成员的两个记录。

    MemberID    P1StatusDate    P2StatusDate   P3StatusDate   P4StatusDate
    10            Null           Null           Null            08/19/2017
    10            Null           Null           Null            08/20/2017

However, this will also cause each statusdate record to appear on a different line for other members:

但是，这也会导致每个statusdate记录显示在其他成员的不同行上：

    MemberID  P1StatusDate  P2StatusDate   P3StatusDate  P4StatusDate
     15         01/01/2017    NULL           NULL          NULL
     15                       03/01/2017     NULL          NULL
     15          NULL         NULL           04/01/2017    NULL
     15          NULL         NULL           NULL          05/01/2017

Is there a way to keep the grouping so that all the status dates are grouped on one line, but also keeps records where there are multiple records with the same status as I indicated above?

有没有办法保持分组，以便所有状态日期分组在一行，但也保留记录，其中有多个记录具有我上面指出的相同状态？

2 个解决方案

#1

aggregate over an aggregated derived table

聚合在聚合派生表上

select memberid, max(p1date), max(p2date), max(p3date), max(p4date)
from (
select  ..... -- your raw grouping with nulls 
from table
group by ...
) x
group x.memberid

#2

From your SQL query

从您的SQL查询

WITH t_status AS
(
SELECT memberid, [status],
       CASE WHEN [status] = 'Applicant' THEN statusdate ELSE NULL END P1StatusDate,
       CASE WHEN [status] = 'Pending' THEN statusdate ELSE NULL END P2StatusDate,
       CASE WHEN [status] = 'In Progress' THEN statusdate ELSE NULL END P3StatusDate,
       CASE WHEN [status] = 'Approved' THEN statusdate ELSE NULL END P4StatusDate,
       ROW_NUMBER() OVER (PARTITION BY memberid, [status] ORDER BY statusdate) rnum
  FROM t_member_status -- your table name
)

SELECT memberid,
       MAX(P1StatusDate) P1StatusDate,
       MAX(P2StatusDate) P2StatusDate,
       MAX(P3StatusDate) P3StatusDate,
       MAX(P4StatusDate) P4StatusDate
  FROM t_status
 GROUP BY memberid, rnum
 ORDER BY memberid, rnum

Result (added a sample data memberid 20 to simulate other records with other values of status and with the same status as well for the same memberid)

结果（添加了一个示例数据memberid 20来模拟具有其他状态值的其他记录，并且对于同一个memberid具有相同的状态）

memberid    P1StatusDate    P2StatusDate    P3StatusDate    P4StatusDate
10          NULL            NULL            NULL            2017-08-19
10          NULL            NULL            NULL            2017-08-20
15          2017-01-01      2017-03-01      2017-04-01      2017-05-01
20          2017-01-01      2017-03-01      2017-04-01      2017-05-01
20          NULL            NULL            NULL            2017-05-02

#1