How do I group columns

• cmrhema

  SSCommitted

  Points: 1558

  More actions

  November 30, 2009 at 7:20 pm

  #139747

  Hi,

  I think I am posting here after a long hiatus.

  I have tables as below

  Table Name : Emp

  Columns:

  Name EmpID

  A 101

  B 102

  C 103

  Table Name : EmpDept

  EmpID DeptID

  101 201

  101 202

  101 203

  102 201

  102 202

  103 203

  Table Name : Department

  DeptID Description CategoryID

  201 .Net 1

  202 Sql 1

  203 Oracle 2

  The values of CategoryID will not be in any table.

  They are defined as enumerator in Asp.net as below

  1--Microsoft Technologies

  2--Oracle

  I want the result as below

  EmpID Department

  101 MicrosoftTechnologies,Oracle

  102 MicrosoftTechnologies

  103 Oracle

  What I did, was a simple inner join between all these tables as

  select Emp.Name ,Department.Description,Department.CategoryID from Emp

  inner join EmpDept on Emp.EmpID=EmpDept.EmpID

  inner join Department on EmpDept.DeptID =Department.DeptID

  So I got the results as

  Name Dept CategoryID

  A .Net 1

  A Sql 1

  A Oracle 2

  B .Net 1

  B Sql 1

  C Oracle 2

  Can anyone pleas help me to group up all the respective Depts into one row viz

  EmpID Department

  101 1,2

  102 1

  103 2

  I want the above result from the query, so that I can use it and modify the DeptID to the name in my application.

  Help would be very much appreciated.

  Thanks and Regards

  cmrhema

• Garadin

  One Orange Chip

  Points: 29613

  More actions

  November 30, 2009 at 8:06 pm

  #1085620

  Welcome back. Just as a refresher, you might want to take a look over the article on posting sample data in my signature for any future posts.

  If you're going to be modifying the output in .NET anyways, I'd advise just passing back the dataset and using .NET to do the concatenation as well. Modify your query to SELECT DISTINCT and don't pull in the department description and you'll get output like this:

  A 1

  A 2

  B 1

  C 2

  There are a lot of methods to do the concatenation in T-SQL (see the string concatenation link in my signature and look at the FOR XML PATH method in particular), but I'd probably do this client side. If you concatenate it first, you're looking at possibly having to parse the string to do the replaces in the front end, so you're doubling the effort.

  Seth Phelabaum

  ---

  Consistency is only a virtue if you're not a screwup. 😉

  Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]