sql server - In sql how to seperate the column values with comma -

sql server - In sql how to seperate the column values with comma -

i have table grouping groupid(pk),sensorid(fk) sensor table. , sensor table has unitsid foreign key referance. used query columns 3 table.

select * grouping left bring together 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

now problem is

i want output like

g1 u1 s1,s3,s4 g1 u2 s2,s5 g2 u1 s7

how output 'grouping' table?

with cte ( select * fromgrouping left bring together sensors on grouping.sensorid = sensors.sensorid left bring together units ongrouping.unitsid = units.unitsid ) select t1."group", t1.unit , stuff(( select ', ' + t2.sensor grouping t2 t2."group" = t1."group" , t2.unit = t1.unit xml path ('')) ,1,2,'') sensors grouping t1 grouping t1."group", t1.unit;

it does'nt work because grouping table has sensorid , unitsid. throws invalid column error.

need help

in sql server, there no built in way so, however, can utilize for xml this:

with cte ( -- set query here ) select t1."group", t1.unit , stuff(( select ', ' + t2.sensor cte t2 t2."group" = t1."group" , t2.unit = t1.unit xml path ('')) ,1,2,'') sensors cte t1 grouping t1."group", t1.unit; sql fiddle demo

this give you:

| grouping | unit | sensors | ----------------------------- | g1 | u1 | s1, s4, s3 | | g1 | u2 | s2, s5 | | g2 | u1 | s7 |

sql sql-server