Results 1 to 5 of 5

Thread: Group By Help

  1. #1
    Join Date
    Nov 2008
    Posts
    117

    Unanswered: 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

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This can be done using a Common Table Expression (CTE).
    CTEs are useful for solving several thorny sql problems.
    In the link below, I outline step-by-step how to create CTEs for several common issues, including string concatenation.
    https://docs.google.com/present/edit...qY2RqdzU&hl=en
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2008
    Posts
    117
    hi thankyou, i tried this, whats wrong with this ?
    Code:
    with cte as
    (select  p.project_name as ProjectName,
    		 b.build_name as BuildName,
             convert(varchar(200),e.emp_name) as EmpName,
    	     e.emp_name as LastNameUsed
    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
    	group by p.project_name,b.build_name,e.emp_name
    union all
    select cte.ProjectName,
    	   BuildName,
    	   convert(varchar(200),cte.EmpName, + ',' + e.emp_name) as EmpName 
    	   e.emp_name as LastNameUsed,
    from cte
    	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)
    select cte.ProjectName,
    		cte.BuildName,
    		cte.EmpName
    from cte

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    It's a little hard to be sure. Does it return an error, or does it just return the wrong results?

  5. #5
    Join Date
    Nov 2008
    Posts
    117
    hi,
    It says many error and i am not sure about its syntax but i tried this too and it display wrong results
    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
    Result is
    project_name | build_name | EmpName
    sony | b1 | aaa,bbb,ccc
    samsung | b3 | aaa,bbb,ccc

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •