0

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
Shiva Kumar
  • 33
  • 1
  • 7

0 Answers0