Geeks With Blogs
Bunch's Blog One day I'll have a catchy subtitle, one day

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.

Technorati Tags:
Posted on Monday, September 20, 2010 10:53 AM | Back to top

Comments on this post: Combining Multiple Values in a Single Field

# re: Combining Multiple Values in a Single Field
Requesting Gravatar...
Sweet example! Thanks very much
Left by BigJim on Sep 21, 2010 9:04 AM

Your comment:
 (will show your gravatar)

Copyright © Bunch | Powered by: