Group By Help

[bharanidharanit]

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

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

[GaryMazzone]

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.

[bharanidharanit]

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

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

[bharanidharanit]

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

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