I want to optimize the query to a simple one by having one select statement instead of these many sub queries and by converting the outer apply to left outer join. is it possible?
select (select LTRIM(pr.ProcRle)+',' as [text()]
from ( select c1.ProcRle, RN=MIN(c1.rn1)
from (select RN1, fai.ProcRle
from #AuditPattern fai
where fai.IncKey=i.IncKey
) c1
outer apply (
select top 1 e=c2.rn1
from (select RN1, ProcRle
from #AuditPattern fai
where fai.IncKey=i.IncKey
) c2
where
c1.RN1<c2.RN1 and c1.ProcRle <> c2.Procl
order by c2.RN1) x
group by x.e, c1.ProcRle
) msq2
join ProcRle pr on msq2.ProcRle=pr.ProcRle
order by msq2.RN
for XML PATH ('')) as [Path]
from FcIncs fi
join incdnt i on fi.IncKey=i.IncKey