Group By clause returning too many rows

Question

I have a table I'm trying to sort by column values in Stage.

It looks something like this:

CaseID	Stage	EventDate
1	A	01/01/10
1	B	01/03/10
1	B	01/04/10
1	C	01/05/10
2	A	02/01/10
2	B	02/02/10
2	C	02/03/10
2	C	02/05/10

I'm trying to organize the data by the Stage so that only the latest EventDate is shown - something like this:

CaseID	A	B	C
1	01/01/10	01/04/10	01/05/10
2	02/01/10	02/02/10	02/05/10

I did a group by statement

SELECT 
    CaseID,
    CASE WHEN Stage = 'A' THEN MAX(EventDate) END AS A,
    CASE WHEN Stage = 'B' THEN MAX(EventDate) END AS B,
    CASE WHEN Stage = 'C' THEN MAX(EventDate) END AS C
FROM
    StageTable
GROUP BY 
    CaseID, Stage

But this returned too many rows with NULL placeholders:

CaseID	A	B	C
1	01/01/10	NULL	NULL
1	NULL	01/04/10	NULL
1	NULL	NULL	01/05/10
2	02/01/10	NULL	NULL
2	NULL	02/02/10	NULL
2	NULL	NULL	02/05/10

I'd like for each row to condense, but I don't know where I went wrong. I've seen other questions with similar questions, but they all seemed to have issues with joint tables showing duplicate results.

Any suggestions would be helpful

Answer 1

You want one result row per CaseID, so GROUP BY CaseID only. Then use conditional aggregation, i.e. put the conditions inside MAX().

SELECT
  CaseID
  ,MAX(CASE WHEN Stage = 'A' THEN EventDate END) AS A
  ,MAX(CASE WHEN Stage = 'B' THEN EventDate END) AS B
  ,MAX(CASE WHEN Stage = 'C' THEN EventDate END) AS C
FROM StageTable
GROUP BY CaseID
ORDER BY CaseID;