sql - How to group and concatenate query results -

say have query this:

select      dept,      person_id form      depts 

which returns following result

dept              person_id -----             --------- 'sales'              2 'management'         2 'sales'              3 'administrative'     4 'management'         4 

how can make query returns following result ?

 depts                         person_id  -----                         --------- 'sales, management'             2 'sales'                         3 'administrative, management'    4 

not postgresql work ms sql server:

select person_id,        (select stuff((select ', ' + dept tablename t2         t2.person_id = t1.person_id         xml path('')), 1, 2, '')) depts  tablename t1 group person_id 

edit:

;with cte as(your super big query here)  select person_id,        (select stuff((select ', ' + dept cte t2         t2.person_id = t1.person_id         xml path('')), 1, 2, '')) depts  cte t1 group person_id