SQL Table- Combine Rows Based on Conditions

Question

I have a table like

DateCol       TaskID     EventName
2017-09-25    10         A
2017-07-26    10         B
2017-07-27    10         A
2017-09-14    10         A
2017-07-25    10         B
2017-09-25    10         C
2017-09-28    11         A
2017-11-01    11         D
2017-11-01    11         C

.....many thousands of rows

What I'd like is an output that, for each TaskID, if events A and C (not any other combination) occurred on the same day (from DateCol), EventName column is "A+C". So in this case it would show

DateCol       TaskID     EventName
2017-09-25    10         A+C
2017-07-26    10         B
2017-07-27    10         A
2017-09-14    10         A
2017-07-25    10         B
2017-09-28    11         A
2017-11-01    11         D
2017-11-01    11         C

You can see that row

2017-09-25    10         A

and

2017-09-25    10         C

are now one row,

2017-09-25    10         A+C

but nothing else is changed.

What kind of SQL statement would I have to do to make such an adjustment?

Answer 1

assumption : only combine 2 different EventName into 1.

a bit crude, but it should work.

; with
cte as
(
    select  t.DateCol, t.TaskID, 
        t.EventName,
        new_EventName   = 
            case when   t.EventName in ('A')        
                 and    max(t.EventName) over (partition by t.DateCol, t.TaskID)    = 'C'
                 then   'A+C'
                 when   t.EventName in ('C')
                 and    min(t.EventName) over (partition by t.DateCol, t.TaskID)    = 'A'
                 then   'A+C'
                 else   NULL
                 end
    from    yourtable t
)
select  distinct DateCol, TaskID, EventName = coalesce(new_EventName, EventName)
from    cte 

if there are other scenario that this does not work, please post the sample data with the expected result