How to separate column values with comma

Question

I have table Grouping Groupid(PK), Sensorid(FK) from Sensor table. And Sensor table has Unitsid as Foreign key reference. So I used this query to get all the columns from three tables:

  Select * from Grouping
  left join Sensors on Grouping.SensorID = Sensors.SensorID
  left joinUnits on Grouping.UnitsID = Units.UnitsID 

Output:

Group  Unit    Sensor
G1  U1  S1
G1  U2  S2
G1  U1  S4
G1  U1  S3
G1  U2  S5
G2  U1  S7

My problem is I want the output like:

G1  U1  S1,S3,S4
G1  U2  S2,S5
G2  U1  S7

How to get this output from 'Grouping' table?

WITH CTE
AS
(
   Select * fromGrouping
  left join Sensors on Grouping.SensorID = Sensors.SensorID
  left join Units onGrouping.UnitsID = Units.UnitsID 
)
SELECT 
  t1."Group",
  t1.Unit ,
  STUFF((
    SELECT ', ' + t2.Sensor  
    FROM Grouping  t2
    WHERE t2."Group" = t1."Group"
      AND t2.Unit = t1.Unit
    FOR XML PATH (''))
  ,1,2,'') AS Sensors
FROM Grouping  t1
GROUP BY t1."Group", t1.Unit;

This doesn't work because grouping table has only sensorId and unitsId, so it throws an invalid column error.

Answer 1

In SQL Server, there is no built in way to do so, however, you can use the FOR XML to do this:

WITH CTE
AS
(
  -- Put your query here
)
SELECT 
  t1."Group",
  t1.Unit ,
  STUFF((
    SELECT ', ' + t2.Sensor  
    FROM CTE t2
    WHERE t2."Group" = t1."Group"
      AND t2.Unit = t1.Unit
    FOR XML PATH (''))
  ,1,2,'') AS Sensors
FROM CTE t1
GROUP BY t1."Group", 
         t1.Unit;

SQL Fiddle Demo

This will give you:

| GROUP | UNIT |    SENSORS |
-----------------------------
|    G1 |   U1 | S1, S4, S3 |
|    G1 |   U2 |     S2, S5 |
|    G2 |   U1 |         S7 |