I have a table with CompanyID, EmployeeID, and RoleID. There are a number of records where the Role is different for the same employee at a company (e.g. John Smith at ABC Inc. is the owner, decision maker and purchasing agent). I need to create a list of roles of each employee at a specific company. I would like the list to concatenate into one comma delimited string.
This is a common and quite reasonable request. The answer is pretty straightforward, too. Unless you are on a database system which has the functionality built in, like MySQL version 4.1 or Sybase ASE, you can't do this with SQL.
Let's say you have a simple query, like this:
select CompanyID , EmployeeID , RoleID from yourtable order by CompanyID , EmployeeID , RoleID
And let's say it returns this result:
CompanyID EmployeeID RoleID ABC 1003 23 ABC 1005 1 ABC 1005 9 ABC 1005 37 DEF 442 9 DEF 442 45 DEF 448 11
In most situations, you would have to write application code to:
loop over the result set
initialize control break variables for CompanyID and EmployeeID
initialize a string for the roles
concatenate each row's RoleID to the string
detect control breaks and output the concatenated string
don't forget to output the last line after end of file!
In MySQL 4.1, though, using the GROUP_CONCAT() function, it's drop dead simple by comparison:
select CompanyID , EmployeeID , group_concat(RoleID order by RoleID) as Roles from yourtable group by CompanyID , EmployeeID order by CompanyID , EmployeeID
Here's what the above query returns:
CompanyID EmployeeID Roles ABC 1003 23 ABC 1005 1,9,37 DEF 442 9,45 DEF 448 11