Removing NULLs after using a case statement and returning multiple results

Question

I use some CASE statements to extract corresponding information from an activity log. The gist of the activity log is the working ID will have multiple activities and dates, here is a simplify i.e.:

+-----+--------------------------+-------------+
| ID  |         activity         |    date     |
+-----+--------------------------+-------------+
| 123 | assigned                 | 2016-01-05  |
| 123 | escalated                | 2016-01-06  |
| 123 | returned from escalation | 2016-01-07  |
| 123 | escalated                | 2016-01-08  |
| 123 | returned from escalation | 2016-01-09  |
| 123 | completed                | 2016-02-06  |
+-----+--------------------------+-------------+

I used multiple CASE statements to turn the results into something like this:

+-----+------------+------------+------------+
| ID  |  assigned  | escalated  |  returned  |
+-----+------------+------------+------------+
| 123 | 2016-01-05 | NULL       | NULL       |
| 123 | NULL       | 2016-01-06 | NULL       |
| 123 | NULL       | NULL       | 2016-01-07 |
| 123 | NULL       | 2016-01-08 | NULL       |
| 123 | NULL       | NULL       | 2016-01-09 |
+-----+------------+------------+------------+

How can I get rid of the NULLs and turn it into something roughly like:

+-----+------------+------------+------------+
| ID  |  assigned  | escalated  |  returned  |
+-----+------------+------------+------------+
| 123 | 2016-01-05 | 2016-01-06 | 2016-01-07 |
| 123 | NULL       | 2016-01-08 | 2016-01-09 |
+-----+------------+------------+------------+

The closest I have gotten is by doing what this person has suggested. Unfortunately using MAX or MIN and grouping by ID only give me one unique result. I am currently trying to put the the cases in a CTE, but not sure where to go from there.

Roughly like this doesn't help us answer the question. What exactly do you need it to look like? How do you logically get from the 2nd output to the 3rd? — Jacobm001
– Jacobm001, Commented Jul 17, 2017 at 19:28
It needs to look like the table I demonstrated, but the NULL field under assigned could stay as NULL or capture the first assigned (2016-01-05) OR if there is a second assigned, it would capture that. I don't know how to get from the 2nd to get the 3rd output. And completed activity has no relevance, but just to demonstrate an example of the activity log. — Simon
– Simon, Commented Jul 17, 2017 at 20:17

Gordon Linoff · Accepted Answer · 2017-07-17 19:26:18Z

1

You can do this using conditional aggregation with row_number():

select t.id,
       max(case when activity = 'assigned' then date end) as assigned,
       max(case when activity = 'escalated' then date end) as escalated,
       max(case when activity = 'returned from escalation ' then date end) as returned
from (select t.*,
             row_number() over (partition by id, activity order by date) as seqnum
      from t
     ) t
group by id, seqnum;

answered Jul 17, 2017 at 19:26

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Simon Over a year ago

Exactly what I needed, thank you! Can you explain what is happening with the following statement: select t.*, row_number() over (partition by id, activity order by date) as seqnum from t What exactly is row_number over and partition doing in conjunction?

Collectives™ on Stack Overflow

Removing NULLs after using a case statement and returning multiple results

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related