CODEWITHSUNDEEP
sqlsql-server
Simon
Simon

Reputation: 1211

Removing NULLs after using a case statement and returning multiple results

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.

Upvotes: 0

Views: 1157

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

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;

Upvotes: 1

Related Questions