Here is a simple way to combine values into one field using T-SQL. This method is particularly handy when you don’t know ahead of time how many values will be returned. This simple example would return a student’s name and list of professors (separated by commas).
SELECT stu.FullName,
(SELECT prof.FullName + ', '
FROM tblProfessors prof
INNER JOIN tblStuProf stuprof ON prof.ProfID = stuprof.ProfID
WHERE stuprof.StuID = 1234
FOR XML PATH('')) AS Professors
from tblStudents stu
where stu.StuID = 1234
The Professors field is combined using the FOR XML PATH line. Without it you would get a ‘Subquery returned more than 1 value. This is not permitted…’ error. The only thing left to do would be to clean up the last comma and space since the returned Professor field will look like: Dr. Jones, Mr. Smith, Mrs. Williams, . A way to handle that is in the code calling the data. An example using VB.Net would be to load the data into a datatable and then remove the last two characters.
Dim profs As String = dt.Rows(0).Item("Professors")
profs = profs.Remove(profs.Length - 2)
dt.Rows(0).Item("Professors") = profs
That is just a simple example and the best way to handle it really depends on your code. For a lot more information on combining multiple values in a single field you can see this site.