In sql server how to seperate the column values with comma

Question

My query is:

select 
gp.GroupName+ '' As GroupName,  
us.UnitsName+'' As Unitname,
sp.Sensorname+',' As sensorName
from Grouping as gp
inner join Units as us
on gp.UnitsID = us.UnitsID
inner join Sensors as sp
on gp.SensorID = sp.SensorID

The output is

GroupName   Unitname    sensorName

G1         Electricity  S1,

G1        Electricity   S3,

G2          Gas       test,

But the output should look like

G1         Electricity  S1,S3

G2          Gas       test

How to get that?

Answer 1

WITH CTE
AS
(
    select
      gp.GroupName  As GroupName,
      us.UnitsName  As Unitname,
      sp.Sensorname As sensorName
    from Grouping as gp
    inner join Units as us  on gp.UnitsID = us.UnitsID
    inner join Sensors as sp on gp.SensorID = sp.SensorID
)
SELECT 
  c1.Groupname,
  c1.Unitname,
  STUFF((
    SELECT ', ' + c2.sensorName
    FROM CTE c2
    WHERE c2.GroupName = c1.GroupName
      AND c2.Unitname = c1.Unitname
    FOR XML PATH (''))
  ,1,2,'') AS Sensors
FROM CTE  c1
GROUP BY c1.GroupName,
         c1.Unitname;