sql result column accumulated with commas

Question

I have three tables

providers

provider_id     provider_name         email
   1              abc                 abc@gmail.com
   2              xyz                 xyz@gmail.com

speciality

speciality_id    speciality_name
   1             Derma
   2             Ortho

provider_speciality

Provider_id       speciality_id
   1                 1
   1                 2

I need an input like below

Providername      Speciality_name   email
abc               Derma,Ortho       abc@gmail.com

below is my query

select 
a.provider_name,
STUFF((SELECT ', ' + ff.speciality_name
          FROM speciality ff
          WHERE ff.speciality_id = pe.speciality_id
          FOR XML PATH('')), 1, 1, '') 
          speciality_name,
a.email
    from providers a 
    left join providers_speciality pe on a.provider_id = pe.provider_id 
              and ISNULL(1,pe.speciality_id) = pe.speciality_id
    left join speciality f with (nolock) on pe.speciality_id = f.speciality_id
    where 
     upper(a.provider_name) like upper(ISNULL('%abc%',a.provider_name)) 
     group by a.provider_name,a.email,pe.speciality_id

It is giving below result

Providername      Speciality_name   email
abc               Derma       abc@gmail.com
abc               Ortho       abc@gmail.com

It may be because of group by of pe.speciality_id

but if I remove that from group by clause below error is coming

providers_speciality.speciality_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Answer 1

You can eliminate GROUP BY like so:

SELECT providers.*, STUFF((
    SELECT ',' + speciality_name
    FROM provider_speciality
    JOIN speciality ON provider_speciality.speciality_id = speciality.speciality_id
    WHERE provider_speciality.provider_id = providers.provider_id
    FOR XML PATH('')
), 1, 1, '') AS speciality_names
FROM providers

Demo on db<>fiddle