Calculating Main Current Task column by concatenating 2 task names based on multiple conditions

Hello, I am trying to solve the problem if someone can help that would be appreciated. I am beginning my journey with SQL.   Desired Output - Main Current Task Column   Task ID column is in ASCEN...