Multiple Joins in SQL making report really slow-CodePudding

I am trying to return all languages spoken by an individual in a single field to use in a SSRS report.

SELECT
    CONCAT (English.LanguageName, ASL.LanguageName, Amharic.LanguageName
    --etc. for all languages 
LEFT OUTER JOIN 
    vwPractitionerLanguages English
ON English.PractitionerID = Demo.PractitionerID
AND English.LanguageID = '13272'
LEFT OUTER JOIN 
    vwPractitionerLanguages ASL
ON ASL.PractitionerID = Demo.PractitionerID
AND ASL.LanguageID = '35050'
LEFT OUTER JOIN 
    vwPractitionerLanguages Amharic
ON Amharic.PractitionerID = Demo.PractitionerID
AND Amharic.LanguageID = '35001'

This has over 200 joins and a very long CONCAT column. What is more efficient and would speed up the query?

CodePudding user response：

Using the STUFF() function, combined with a FOR XML sub-query, is a reasonably well-known method to achieve the result you're looking (that is, take column values from multiple rows and output as a single, concatenated value)

SELECT d.PractitionerID, 
STUFF(
  (SELECT ','   v.LanguageName
   FROM vwPractitionerLanuages v
   WHERE v.PractitionerID = d.PractitionerID
   ORDER BY v.LanguageName
   FOR XML PATH('')), 1, 1, '') AS [LanguagesSpoken]
FROM [Demo] d

There is a SQL function named STRING_AGG() which achieves the same result, however I believe that function is only supported in SQL 2017 and above (you've tagged your question with SSRS-2012, so I'm making an assumption that your database engine version is also 2012)

CodePudding user response：

To get a comma separated list by user:

SELECT PractitionerID, STRING_AGG(LanguageName, ', ') as LangList 
FROM vwPractitionerLanguages
GROUP BY PractionerID

You could join to that and it would be fast. (I used Demo.*, but it is better not to use a wildcard.)

SELECT X.Lang, Demo.*
FROM Demo
LEFT JOIN (
  SELECT PractitionerID, STRING_AGG(LanguageName, ', ') as LangList 
  FROM vwPractitionerLanguages
  GROUP BY PractionerID
) X on X.PractitionerID = Demo.PractitionerID