Group By Help

Hi,
I am Microsoft SQL Server 2005. I am fetching only distinct rows from the database and the code is

Code:

---

select distinct p.project_name,build_name,emp_name
from project_master p
        inner join build_master b on b.proj_id = p.project_id
        inner join project_schedule s on s.build_id = b.build_id
        inner join emp_master e on e.emp_id = s.emp_id

---

it returns rows like this
project_name | build_name | emp_name
sony | b1 | aaa
sony | b1 | bbb
samsung | b3 | ccc
samsung | b3 | bbb

Is it possible to concatenate emp_name when other two are distinct like this
project_name | build_name | emp_name
sony | b1 | aaa,bbb
samsung | b3 | ccc,bbb

Have you tried GOOGLE for this? There are multiple ways of doing this. It will depend on how much data there is on how fast the methods are. I currently use one method that tables about 1 min to process 3 million rows doing this. When I tried another method it was over 1.5 hours and I just stoped the process.

yes, group_concat method in mysql, and also found alternate for this, but i dint anything in that which uses xml etc. and so came for help

Code:

---

select column_name as [Text()]
from table_name
for xml path('')

---

whats wrong in this code? this gives me all the employee list like this.
Result is
project_name | build_name | EmpName
sony | b1 | aaa,bbb,ccc
samsung | b3 | aaa,bbb,ccc

Code:

---

select distinct p.project_name, b.build_name,
                        (select em.emp_name + ',' as [text()]
                                from emp_master em
                                inner join project_master p1 on p1.project_id = p.project_id
                                for xml path('')) as EmpName
                from project_master p
        inner join build_master b on b.proj_id = p.project_id
        inner join project_schedule s on s.build_id = b.build_id
        inner join emp_master e on e.emp_id = s.emp_id

---