[Solved] Optimize query using Concat method

by


I would try with this :

SELECT [CaseID], 
       STUFF( (SELECT CONCAT('; ', A.[AssignedPathologist])
               FROM CTE1 A  
               WHERE A.[CaseID] = B.[CaseID] 
               FOR XML PATH('') 
               ),1, 1, ''
            ) As [AssignedPathologist]  
FROM (SELECT DISTINCT CaseID CTE1 B) B;

For newer versions you can use string_agg() :

SELECT CASEID, STRING_AGG(AssignedPathologist, '; ') AS AssignedPathologist
FROM CTE1 C1
GROUP BY CASEID;

5

solved Optimize query using Concat method