How do I go about type iterations in a SQL Query?
I would just join the tables directly
Coalesce is fine here I think  

SELECT d.[Name] as DepName
      ,[ParentEntityType]
      ,[ParentNetObjectID]
      ,[ChildEntityType]
      ,[ChildNetObjectID]
      ,coalesce(con.Name, nod.Caption, trans.name, app.name) as [ParentName]
      ,coalesce(con.status, nod.status, trans.laststatus, app.Availability) as [ParentStatus]
FROM dbo.Dependencies as d
LEFT JOIN dbo.containers as con 
  on d.ParentNetObjectID = con.ContainerID 
 AND d.ParentEntityType = 'Orion.Groups'
LEFT JOIN  ...  
ORDER BY ParentName 


You could put the 3 tables in a view or cte  

select "orion" as type, 
        c.ContainerID
       ,c.Status
       ,c.Name
 FROM dbo.containers as c 
union all 
...