SQL Server Group By with Group Concat

Question

I have a table like this

Hospital    Insurance   PatientCount
H1          I1          1
H1          I1          2
H2          I1          1
H2          I2          1

Want to group this table by insurance as,

Hospital    Insurance   PatientCount
H1,H2           I1          4
H2              I2          1

Tried using

select 
stuff((select ', ' + Hospital
from  Insurances 
where (InsuranceName = i.InsuranceName)
for xml path(''),type).value('(./text())[1]','varchar(max)')
  ,1,2,'') as Hospitals,
i.InsuranceName,
sum(i.PatientsCount)
from Insurances i
group by i.InsuranceName;

Output :

Hospital    Insurance   PatientCount
H1,H1,H2        I1          4
H2              I2          1

Answer 1

Just needed to add DISTINCT to the STUFF.

select 
stuff((select DISTINCT ', ' + Hospital
from  A 
where (InsuranceName = i.InsuranceName)
for xml path(''),type).value('(./text())[1]','varchar(max)')
  ,1,2,'') as Hospitals,
i.InsuranceName,
sum(i.PatientCount)
from A i
group by i.InsuranceName;

Answer 2

This syntax works:

DECLARE @t table
(Hospital char(2), InsuranceName char(2), PatientCount int)
INSERT @t values
('H1','I1',1),
('H1','I1',2),
('H2','I1',1),
('H2','I2',1)


SELECT 
    STUFF(( 
        SELECT ',' + [Hospital] 
        FROM @t t1 
        WHERE t1.InsuranceName = t.InsuranceName
        GROUP BY  [Hospital] 
        for xml path(''), type
    ).value('.', 'varchar(max)'), 1, 1, '') Hospital,
    InsuranceName, 
    SUM(PatientCount) [Patientcount] 
FROM @t t 
GROUP BY InsuranceName

Result:

Hospital  InsuranceName  Patientcount
H1,H2     I1             3
H2        I2             1