Multiple Joins in SQL making report really slow

Question

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

    SELECT DISTINCT
 -- #Selection Fields
 Demo.FirstName
 ,Demo.LastName
 ,Demo.MiddleName
 ,Demo.NationalProviderID AS [NPI 1]
 ,Demo.PractitionerFormalTitle AS [License abv]
 ,Demo.ReceivedDate AS [Start Date]
 ,Demo.Notes AS [Subspecialties]
 ,Demo.SocialSecurityNumber
 ,Demo.GenderName
 ,Demo.BirthDate
 ,Demo.CompletedDate AS [Original Delegation Date]
 ,Demo.RenewalDate AS [Recred Due]
 ,Demo.CompletedDate AS [Last Recred Completed]

 ,CAQH.IDNumber AS [CAQH Number]

 ,TIN.TaxIDNumber AS [TIN]

 ,Location.PracticeNationalProviderID AS [NPI 2]
 ,Location.LineNumber1 AS [Location 1]
 ,Location.LineNumber2 AS [Location 2]
 ,Location.City AS [Location City]
 ,Location.[State] AS [Location State]
 ,Location.ZipCode AS [Location Zip]
 ,Location.TwentyFourHourCoverage [ADA Compliant]
 ,Location.PracticeLegalName



 ,Medicare.ProviderNumber [Medicare Number]

 ,Education.DegreeName
 ,Education.InstitutionName AS [School Name]
 ,Education.DateFrom AS [School Start Date]
 ,Education.DateTo AS [School End Date]

 ,SchoolAddress.LineNumber1 AS [School Line1]
 ,SchoolAddress.LineNumber2 AS [School Line2]
 ,SchoolAddress.City AS [School City]
 ,SchoolAddress.[STATE] AS [School State]
 ,SchoolAddress.ZipCode AS [School Zip]

 ,State.IssuingState AS [License State]
 ,State.LicenseNumber AS [State License Number]
 ,State.IssueDate AS [State Issue Date]
 ,State.ExpirationDate AS [State Expiration Date]
 ,State.ActiveStatePractice AS [Medicare Products y/n]

 ,DEA.LicenseNumber AS [DEA Number]
 ,DEA.IssueDate AS [DEA Issue Date]
 ,DEA.ExpirationDate AS [DEA Expiration]
 ,DEA.IssuingState AS [DEA State]

 ,CDS.LicenseNumber AS [CDS Number]
 ,CDS.IssueDate AS [CDS Issue Date]
 ,CDS.ExpirationDate AS [CDS Expiration]

 ,Specialties.TaxonomyCode
 ,Specialties.BoardStatusTypeName AS [Board Name]
 ,Specialties.BoardStatusTypeID
 ,Specialties.CertificationDate
 ,Specialties.ExpirationDate AS [Board Expiration]
 ,Specialties.CertificationNumber

 ,Email.EmailAddress

Currently I have this long Concat that brings together all the languages that are linked to a person.

 ,CONCAT (English.LanguageName, ASL.LanguageName,                                                 
Amharic.LanguageName, Swahili.LanguageName, Anang.LanguageName, 
Arabic.LanguageName, Armenian.LanguageName, 
Azarbaijani.LanguageName, Azeri.LanguageName, 
Bengali.LanguageName, Bulgarian.LanguageName, 
CanadianFrench.LanguageName, Cantonese.LanguageName, 
Chaldean.LanguageName, Chinese.LanguageName, Creole.LanguageName, 
Croatian.LanguageName, Czech.LanguageName, Danish.LanguageName, 
Dari.LanguageName, Dutch.LanguageName, Efik.LanguageName, 
Farsi.LanguageName, Filipino.LanguageName, Flemish.LanguageName, 
French.LanguageName, German.LanguageName, Greek.LanguageName, 
Gujarati.LanguageName, Haitian.LanguageName, Hakkai.LanguageName, 
Hebrew.LanguageName, Hindi.LanguageName, Hindustani.LanguageName, 
Llocano.LanguageName, Indian.LanguageName, Italian.LanguageName, 
Japanese.LanguageName, Kannada.LanguageName, 
Kiswahili.LanguageName, Konkani.LanguageName, 
Korean.LanguageName, Kurdish.LanguageName, Laotian.LanguageName, 
Latvian.LanguageName, Lithuanian.LanguageName, 
Macedonian.LanguageName, Malayalam.LanguageName, 
MandarinChinese.LanguageName, Marathi.LanguageName, 
Marwari.LanguageName, Nigerian.LanguageName, 
Norwegian.LanguageName, oriya.LanguageName, Pahari.LanguageName, 
Pashto.LanguageName, Persian.LanguageName, 
Philipino.LanguageName, Polish.LanguageName, 
Portuguese.LanguageName, Punjabi.LanguageName, 
Romanian.LanguageName, Russian.LanguageName, 
Sanskrit.LanguageName, Serbian.LanguageName, 
Sicilian.LanguageName, Sindhi.LanguageName, 
Sinhalese.LanguageName, Slavic.LanguageName, 
Spanish.LanguageName, Syriac.LanguageName, Tagalog.LanguageName, 
Taiwanese.LanguageName, Tamil.LanguageName, Telugu.LanguageName, 
Thai.LanguageName, Tigrinya.LanguageName, Turkish.LanguageName, 
Ukrainian.LanguageName, Urdu.LanguageName, 
Vietnamese.LanguageName, Yoruba.LanguageName, 
Yugoslavian.LanguageName) AS [Languages]

-- #endSelcetion fields

FROM  vwPractitionerDemographics Demo

LEFT OUTER JOIN vwPractitionerEmails Email
    ON Email.PractitionerID = Demo.PractitionerID
        AND Email.EmailTypeID = '1111197'

LEFT OUTER JOIN vwPractitionerEducation Education
    ON Education.PractitionerID = Demo.PractitionerID
        AND Education.Completed = 'Y'

LEFT OUTER JOIN vwInstitutionAddresses SchoolAddress
    ON SchoolAddress.InstitutionID = Education.InstitutionID

LEFT OUTER JOIN vwPractitionerSpecialties Specialties
    ON Specialties.PractitionerID = Demo.PractitionerID
        AND Specialties.PrimarySpecialty = 'Y'
        And Specialties.CertificationNumber Is Not null 
        
LEFT OUTER JOIN vwPractitionerAlternateIDs CAQH
    ON CAQH.ProviderID = Demo.PractitionerID
        AND CAQH.AlternateIDTypeID = '1111033'

LEFT OUTER JOIN vwPractitionerProducts Plans
    ON Plans.PractitionerID = Demo.PractitionerID

--Join all Licenses by Types

LEFT OUTER JOIN vwPractitionerLicenses State
    ON Demo.PractitionerID = State.PractitionerID
        AND state.LicenseTypeCode = 'State'
        And state.Archived = 'N'

LEFT OUTER JOIN vwPractitionerLicenses DEA
    ON Demo.PractitionerID = DEA.PractitionerID
        AND DEA.LicenseTypeCode = 'DEA'
        AND DEA.IssuingState = State.IssuingState

LEFT OUTER JOIN vwPractitionerLicenses CDS
    ON Demo.PractitionerID = CDS.PractitionerID
        AND CDS.LicenseTypeCode = 'CDS'
        And CDS.IssuingState = State.IssuingState

--Locations linked to applicable licenses

LEFT OUTER JOIN vwPractitionerLocations Location
    ON Plans.PractitionerID = Location.PractitionerID
        AND Location.[State] = State.IssuingState
        And Location.PracticeID = Plans.PracticeID

Left Outer Join vwPractitionerProducts Medicare
    On Medicare.PractitionerID = Demo.PractitionerID
        And Medicare.PracticeID = Location.PracticeID
        And Medicare.ProviderNumber Is Not null        

Left Outer Join vwPracticeLocations TIN 
    On TIN.PracticeID = Location.PracticeID

So there are currently all the following Joins to query the same table for possible languages that could be linked to a person.

--Joins or each language so each language can be queried 
separately and placed in the same cell for all languages that are 
present in the database

  LEFT OUTER JOIN vwPractitionerLanguages English
    ON English.PractitionerID = Demo.PractitionerID
        AND English.LanguageID = '13272'
  LEFT OUTER JOIN vwPractitionerLanguages Swahili
    ON Swahili.PractitionerID = Demo.PractitionerID
        AND Swahili.LanguageID = '35055'
  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'
  LEFT OUTER JOIN vwPractitionerLanguages Anang
    ON Anang.PractitionerID = Demo.PractitionerID
        AND Anang.LanguageID = '35002'
  LEFT OUTER JOIN vwPractitionerLanguages Arabic
    ON Arabic.PractitionerID = Demo.PractitionerID
        AND Arabic.LanguageID = '13265'
  LEFT OUTER JOIN vwPractitionerLanguages Armenian
    ON Armenian.PractitionerID = Demo.PractitionerID
        AND Armenian.LanguageID = '13266'
  LEFT OUTER JOIN vwPractitionerLanguages Azarbaijani
    ON Azarbaijani.PractitionerID = Demo.PractitionerID
        AND Azarbaijani.LanguageID = '35004'
  LEFT OUTER JOIN vwPractitionerLanguages Azeri
    ON Azeri.PractitionerID = Demo.PractitionerID
        AND Swahili.LanguageID = '1111802'
  LEFT OUTER JOIN vwPractitionerLanguages Bengali
    ON Bengali.PractitionerID = Demo.PractitionerID
        AND Bengali.LanguageID = '35005'
  LEFT OUTER JOIN vwPractitionerLanguages Bulgarian
    ON Bulgarian.PractitionerID = Demo.PractitionerID
        AND Bulgarian.LanguageID = '35006'
  LEFT OUTER JOIN vwPractitionerLanguages CanadianFrench
    ON CanadianFrench.PractitionerID = Demo.PractitionerID
        AND CanadianFrench.LanguageID = '13267'
  LEFT OUTER JOIN vwPractitionerLanguages Cantonese
    ON Cantonese.PractitionerID = Demo.PractitionerID
        AND Cantonese.LanguageID = '35007'
  LEFT OUTER JOIN vwPractitionerLanguages Chaldean
    ON Chaldean.PractitionerID = Demo.PractitionerID
        AND Chaldean.LanguageID = '35008'
  LEFT OUTER JOIN vwPractitionerLanguages Chinese
    ON Chinese.PractitionerID = Demo.PractitionerID
        AND Chinese.LanguageID = '13268'
  LEFT OUTER JOIN vwPractitionerLanguages Creole
    ON Creole.PractitionerID = Demo.PractitionerID
        AND Creole.LanguageID = '35009'
  LEFT OUTER JOIN vwPractitionerLanguages Croatian
    ON Croatian.PractitionerID = Demo.PractitionerID
        AND Croatian.LanguageID = '35010'
  LEFT OUTER JOIN vwPractitionerLanguages Czech
    ON Czech.PractitionerID = Demo.PractitionerID
        AND Czech.LanguageID = '35011'
  LEFT OUTER JOIN vwPractitionerLanguages Danish
    ON Danish.PractitionerID = Demo.PractitionerID
        AND Danish.LanguageID = '13270'
  LEFT OUTER JOIN vwPractitionerLanguages Dari
    ON Dari.PractitionerID = Demo.PractitionerID
        AND Dari.LanguageID = '35012'
  LEFT OUTER JOIN vwPractitionerLanguages Dutch
    ON Dutch.PractitionerID = Demo.PractitionerID
        AND Dutch.LanguageID = '13271'
  LEFT OUTER JOIN vwPractitionerLanguages Efik
    ON Efik.PractitionerID = Demo.PractitionerID
        AND Efik.LanguageID = '35013'
  LEFT OUTER JOIN vwPractitionerLanguages Farsi
    ON Farsi.PractitionerID = Demo.PractitionerID
        AND Farsi.LanguageID = '35014'
  LEFT OUTER JOIN vwPractitionerLanguages Filipino
    ON Filipino.PractitionerID = Demo.PractitionerID
        AND Filipino.LanguageID = '35016'
  LEFT OUTER JOIN vwPractitionerLanguages Flemish
    ON Flemish.PractitionerID = Demo.PractitionerID
        AND Flemish.LanguageID = '13273'
  LEFT OUTER JOIN vwPractitionerLanguages French
    ON French.PractitionerID = Demo.PractitionerID
        AND French.LanguageID = '13274'
  LEFT OUTER JOIN vwPractitionerLanguages German
    ON German.PractitionerID = Demo.PractitionerID
        AND German.LanguageID = '13275'
  LEFT OUTER JOIN vwPractitionerLanguages Greek
    ON Greek.PractitionerID = Demo.PractitionerID
        AND Greek.LanguageID = '13276'
  LEFT OUTER JOIN vwPractitionerLanguages Gujarati
    ON Gujarati.PractitionerID = Demo.PractitionerID
        AND Gujarati.LanguageID = '13273'
  LEFT OUTER JOIN vwPractitionerLanguages Haitian
    ON Haitian.PractitionerID = Demo.PractitionerID
        AND Haitian.LanguageID = '1116065'
  LEFT OUTER JOIN vwPractitionerLanguages Hakkai
    ON Hakkai.PractitionerID = Demo.PractitionerID
        AND Hakkai.LanguageID = '35018'
  LEFT OUTER JOIN vwPractitionerLanguages Hebrew
    ON Hebrew.PractitionerID = Demo.PractitionerID
        AND Hebrew.LanguageID = '13277'
  LEFT OUTER JOIN vwPractitionerLanguages Hindi
    ON Hindi.PractitionerID = Demo.PractitionerID
        AND Hindi.LanguageID = '35019'
  LEFT OUTER JOIN vwPractitionerLanguages Hindustani
    ON Hindustani.PractitionerID = Demo.PractitionerID
        AND Hindustani.LanguageID = '13278'
  LEFT OUTER JOIN vwPractitionerLanguages Llocano
    ON Llocano.PractitionerID = Demo.PractitionerID
        AND Llocano.LanguageID = '1117304'
  LEFT OUTER JOIN vwPractitionerLanguages Indian
    ON Indian.PractitionerID = Demo.PractitionerID
        AND Indian.LanguageID = '35025'
  LEFT OUTER JOIN vwPractitionerLanguages Italian
    ON Italian.PractitionerID = Demo.PractitionerID
        AND Italian.LanguageID = '13279'
  LEFT OUTER JOIN vwPractitionerLanguages Japanese
    ON Japanese.PractitionerID = Demo.PractitionerID
        AND Japanese.LanguageID = '13280'
  LEFT OUTER JOIN vwPractitionerLanguages Kannada
    ON Kannada.PractitionerID = Demo.PractitionerID
        AND Kannada.LanguageID = '35026'
  LEFT OUTER JOIN vwPractitionerLanguages Kiswahili
    ON Kiswahili.PractitionerID = Demo.PractitionerID
        AND Kiswahili.LanguageID = '35027'
  LEFT OUTER JOIN vwPractitionerLanguages Konkani
    ON Konkani.PractitionerID = Demo.PractitionerID
        AND Konkani.LanguageID = '35028'
  LEFT OUTER JOIN vwPractitionerLanguages Korean
    ON Korean.PractitionerID = Demo.PractitionerID
        AND Korean.LanguageID = '35029'
  LEFT OUTER JOIN vwPractitionerLanguages Kurdish
    ON Kurdish.PractitionerID = Demo.PractitionerID
        AND Kurdish.LanguageID = '35030'
  LEFT OUTER JOIN vwPractitionerLanguages Laotian
    ON Laotian.PractitionerID = Demo.PractitionerID
        AND Laotian.LanguageID = '1116043'
  LEFT OUTER JOIN vwPractitionerLanguages Latvian
    ON Latvian.PractitionerID = Demo.PractitionerID
        AND Latvian.LanguageID = '35031'
  LEFT OUTER JOIN vwPractitionerLanguages Lithuanian
    ON Lithuanian.PractitionerID = Demo.PractitionerID
        AND Lithuanian.LanguageID = '35032'
  LEFT OUTER JOIN vwPractitionerLanguages Macedonian
    ON Macedonian.PractitionerID = Demo.PractitionerID
        AND Macedonian.LanguageID = '35033'
  LEFT OUTER JOIN vwPractitionerLanguages Malayalam
    ON Malayalam.PractitionerID = Demo.PractitionerID
        AND Malayalam.LanguageID = '35034'
  LEFT OUTER JOIN vwPractitionerLanguages MandarinChinese
    ON MandarinChinese.PractitionerID = Demo.PractitionerID
        AND MandarinChinese.LanguageID = '35035'
  LEFT OUTER JOIN vwPractitionerLanguages Marathi
    ON Marathi.PractitionerID = Demo.PractitionerID
        AND Marathi.LanguageID = '35036'
  LEFT OUTER JOIN vwPractitionerLanguages Marwari
    ON Marwari.PractitionerID = Demo.PractitionerID
        AND Marwari.LanguageID = '35037'
  LEFT OUTER JOIN vwPractitionerLanguages Nigerian 
    ON Nigerian.PractitionerID = Demo.PractitionerID
        AND Nigerian.LanguageID = '1117126'
  LEFT OUTER JOIN vwPractitionerLanguages Norwegian
    ON Norwegian.PractitionerID = Demo.PractitionerID
        AND Norwegian.LanguageID = '1116045'
  LEFT OUTER JOIN vwPractitionerLanguages Oriya
    ON Oriya.PractitionerID = Demo.PractitionerID
        AND Oriya.LanguageID = '35038'
  LEFT OUTER JOIN vwPractitionerLanguages Pahari
    ON Pahari.PractitionerID = Demo.PractitionerID
        AND Pahari.LanguageID = '35039'
  LEFT OUTER JOIN vwPractitionerLanguages Pashto
    ON Pashto.PractitionerID = Demo.PractitionerID
        AND Pashto.LanguageID = '35041'
  LEFT OUTER JOIN vwPractitionerLanguages Persian
    ON Persian.PractitionerID = Demo.PractitionerID
        AND Persian.LanguageID = '35042'
  LEFT OUTER JOIN vwPractitionerLanguages Philipino
    ON Philipino.PractitionerID = Demo.PractitionerID
        AND Philipino.LanguageID = '35043'
  LEFT OUTER JOIN vwPractitionerLanguages Polish
    ON Polish.PractitionerID = Demo.PractitionerID
        AND Polish.LanguageID = '13281'
  LEFT OUTER JOIN vwPractitionerLanguages Portuguese
    ON Portuguese.PractitionerID = Demo.PractitionerID
        AND Portuguese.LanguageID = '13282'
  LEFT OUTER JOIN vwPractitionerLanguages Punjabi
    ON Punjabi.PractitionerID = Demo.PractitionerID
        AND Punjabi.LanguageID = '35044'
  LEFT OUTER JOIN vwPractitionerLanguages Romanian
    ON Romanian.PractitionerID = Demo.PractitionerID
        AND Romanian.LanguageID = '35045'
  LEFT OUTER JOIN vwPractitionerLanguages Russian
    ON Russian.PractitionerID = Demo.PractitionerID
        AND Russian.LanguageID = '13283'
  LEFT OUTER JOIN vwPractitionerLanguages Sanskrit
    ON Sanskrit.PractitionerID = Demo.PractitionerID
        AND Sanskrit.LanguageID = '35046'
  LEFT OUTER JOIN vwPractitionerLanguages Serbian
    ON Serbian.PractitionerID = Demo.PractitionerID
        AND Serbian.LanguageID = '35048'
  LEFT OUTER JOIN vwPractitionerLanguages Sicilian
    ON Sicilian.PractitionerID = Demo.PractitionerID
        AND Sicilian.LanguageID = '35049'
  LEFT OUTER JOIN vwPractitionerLanguages Sindhi
    ON Sindhi.PractitionerID = Demo.PractitionerID
        AND Sindhi.LanguageID = '35051'
  LEFT OUTER JOIN vwPractitionerLanguages Sinhalese
    ON Sinhalese.PractitionerID = Demo.PractitionerID
        AND Sinhalese.LanguageID = '35052'
  LEFT OUTER JOIN vwPractitionerLanguages Slavic
    ON Slavic.PractitionerID = Demo.PractitionerID
        AND Slavic.LanguageID = '13284'
  LEFT OUTER JOIN vwPractitionerLanguages Spanish
    ON Spanish.PractitionerID = Demo.PractitionerID
        AND Spanish.LanguageID = '13285'
  LEFT OUTER JOIN vwPractitionerLanguages Syriac
    ON Syriac.PractitionerID = Demo.PractitionerID
        AND Syriac.LanguageID = '35056'
  LEFT OUTER JOIN vwPractitionerLanguages Tagalog
    ON Tagalog.PractitionerID = Demo.PractitionerID
        AND Tagalog.LanguageID = '35057'
  LEFT OUTER JOIN vwPractitionerLanguages Taiwanese
    ON Taiwanese.PractitionerID = Demo.PractitionerID
        AND Taiwanese.LanguageID = '35058'
  LEFT OUTER JOIN vwPractitionerLanguages Tamil
    ON Tamil.PractitionerID = Demo.PractitionerID
        AND Tamil.LanguageID = '35059'
  LEFT OUTER JOIN vwPractitionerLanguages Telugu
    ON Telugu.PractitionerID = Demo.PractitionerID
        AND Telugu.LanguageID = '35060'
  LEFT OUTER JOIN vwPractitionerLanguages Thai
    ON Thai.PractitionerID = Demo.PractitionerID
        AND Thai.LanguageID = '35061'
  LEFT OUTER JOIN vwPractitionerLanguages Tigrinya
    ON Tigrinya.PractitionerID = Demo.PractitionerID
        AND Tigrinya.LanguageID = '35062'
  LEFT OUTER JOIN vwPractitionerLanguages Turkish
    ON Turkish.PractitionerID = Demo.PractitionerID
        AND Turkish.LanguageID = '35063'
  LEFT OUTER JOIN vwPractitionerLanguages Ukrainian
    ON Ukrainian.PractitionerID = Demo.PractitionerID
        AND Ukrainian.LanguageID = '35064'
  LEFT OUTER JOIN vwPractitionerLanguages Urdu
    ON Urdu.PractitionerID = Demo.PractitionerID
        AND Urdu.LanguageID = '35065'
  LEFT OUTER JOIN vwPractitionerLanguages Vietnamese
    ON Vietnamese.PractitionerID = Demo.PractitionerID
        AND Vietnamese.LanguageID = '35066'
  LEFT OUTER JOIN vwPractitionerLanguages Yoruba
    ON Yoruba.PractitionerID = Demo.PractitionerID
        AND Yoruba.LanguageID = '1116005'
  LEFT OUTER JOIN vwPractitionerLanguages Yugoslavian
    ON Yugoslavian.PractitionerID = Demo.PractitionerID
        AND Yugoslavian.LanguageID = '35067'


Where
Demo.Archived = 'N'
And Location.Archived = 'N'
And Plans.Archived = 'N'
AND Demo.ApplicationStatusID NOT IN ('1111152', '1118448')
AND Demo.PractitionerTypeID NOT IN ('1112367', '1119027')
And Location.[State] In (@State)

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

My issue is I don't know which languages a person would have and I need them all to pull in the same column.

To me your current query doesn't quite make sense. I think you can probably use SQL STUFF / FOR XML trick for this, but you'll need to update your question with some information about the structure of your "Demo" table and the "vwPractitionerLanguages" view. And maybe a sample set of data from each of those. Alternatively, you could do your own research on using STUFF / FOR XML in SQL (to get a single field containing concatenated values) — Craig, Jan 31 '23 at 21:10
[How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) [mre] [Tips for asking a good SQL question](https://meta.stackoverflow.com/q/271055/3404097) [Asking query performance questions](https://dba.meta.stackexchange.com/q/3034/43932) [How to Optimize Queries in a Database - The Basics](https://stackoverflow.com/q/3191623/3404097) [What are your most common sql optimizations?](https://stackoverflow.com/q/1332778/3404097) [When and why are database joins expensive?](https://stackoverflow.com/q/173726/3404097) — philipxy, Jan 31 '23 at 22:49
Debug questions require a [mre]. [ask] [Help] Your 1st join has no left table. Please use a tiny table mnemonic alias with every column use. PS Your language names should be data in table rows, not table names or source code. Your language tables should be 1 table. [Strategy for “Which is better” questions](https://meta.stackexchange.com/q/204461/266284) — philipxy, Jan 31 '23 at 23:21

Craig · Accepted Answer · 2023-02-01T22:16:54.563

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 DISTINCT
 -- #Selection Fields
 Demo.FirstName
 ,Demo.LastName

 -- all of your other fields in here, but I haven't included them in this example

,Email.EmailAddress
,
STUFF(
  (SELECT ',' + v.LanguageName
   FROM vwPractitionerLanuages v
   WHERE v.PractitionerID = [Demo].PractitionerID
   ORDER BY v.LanguageName
   FOR XML PATH('')), 1, 1, '') AS [LanguagesSpoken]
FROM  vwPractitionerDemographics Demo
LEFT OUTER JOIN vwPractitionerEmails Email
    ON Email.PractitionerID = Demo.PractitionerID
        AND Email.EmailTypeID = '1111197'

LEFT OUTER JOIN vwPractitionerEducation Education
    ON Education.PractitionerID = Demo.PractitionerID
        AND Education.Completed = 'Y'

LEFT OUTER JOIN vwInstitutionAddresses SchoolAddress
    ON SchoolAddress.InstitutionID = Education.InstitutionID

LEFT OUTER JOIN vwPractitionerSpecialties Specialties
    ON Specialties.PractitionerID = Demo.PractitionerID
        AND Specialties.PrimarySpecialty = 'Y'
        And Specialties.CertificationNumber Is Not null 
        
LEFT OUTER JOIN vwPractitionerAlternateIDs CAQH
    ON CAQH.ProviderID = Demo.PractitionerID
        AND CAQH.AlternateIDTypeID = '1111033'

LEFT OUTER JOIN vwPractitionerProducts Plans
    ON Plans.PractitionerID = Demo.PractitionerID

--Join all Licenses by Types

LEFT OUTER JOIN vwPractitionerLicenses State
    ON Demo.PractitionerID = State.PractitionerID
        AND state.LicenseTypeCode = 'State'
        And state.Archived = 'N'

LEFT OUTER JOIN vwPractitionerLicenses DEA
    ON Demo.PractitionerID = DEA.PractitionerID
        AND DEA.LicenseTypeCode = 'DEA'
        AND DEA.IssuingState = State.IssuingState

LEFT OUTER JOIN vwPractitionerLicenses CDS
    ON Demo.PractitionerID = CDS.PractitionerID
        AND CDS.LicenseTypeCode = 'CDS'
        And CDS.IssuingState = State.IssuingState

--Locations linked to applicable licenses

LEFT OUTER JOIN vwPractitionerLocations Location
    ON Plans.PractitionerID = Location.PractitionerID
        AND Location.[State] = State.IssuingState
        And Location.PracticeID = Plans.PracticeID

Left Outer Join vwPractitionerProducts Medicare
    On Medicare.PractitionerID = Demo.PractitionerID
        And Medicare.PracticeID = Location.PracticeID
        And Medicare.ProviderNumber Is Not null        

Left Outer Join vwPracticeLocations TIN 
    On TIN.PracticeID = Location.PracticeID

Where
Demo.Archived = 'N'
And Location.Archived = 'N'
And Plans.Archived = 'N'
AND Demo.ApplicationStatusID NOT IN ('1111152', '1118448')
AND Demo.PractitionerTypeID NOT IN ('1112367', '1119027')
And Location.[State] In (@State)

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)

I've updated my answer to essentially include the info that you updated in the question (with the exception that I haven't put in every single SELECT column that you have - but you'll be able to incorporate those back in, assuming this solution works for you) — Craig, Feb 01 '23 at 22:17
Thank you so much I am getting a similar error still. "Incorrect syntax near 'ORDER'. Expecting ')', EXCEPT, or UNION." — Regan Brooks, Feb 02 '23 at 13:48
I moved it to the beginning of the query and that seemed to resolve for some reason. Thanks so much for your help! — Regan Brooks, Feb 02 '23 at 13:57

Hogan · Answer 2 · 2023-01-31T23:15:00.293

-2

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

edited Jan 31 '23 at 23:15

answered Jan 31 '23 at 21:02

Hogan

69,564
10
76
117

@Craig -- please look at the new answer -- I miss read the question – Hogan Jan 31 '23 at 22:37
better suggestion, however I believe the STRING_AGG() function is only supported by SQL 2017 and above, so given the OP tagged the question with SSRS-2012, it's probably a reasonable assumption that their database engine is also 2012, and hence won't support that function – Craig Jan 31 '23 at 23:37

Multiple Joins in SQL making report really slow

2 Answers2