How do I concatenate strings from a column into a single row?
This question is asked quite frequently. People want to take a column like this:
And return a resultset like this:
This isn't exactly relational, and can certainly be handled by the presentation layer. However, there are kludges in SQL Server that will allow you to derive a solution. We'll start with the simple case above:
(Note that you can add an ORDER BY clause to the SELECT, but this will not necessarily be obeyed... the resulting list will be displayed in an arbitrary order. Please observe the wording there—I'm not saying your ORDER BY will be ignored, just that it's not guaranteed to be obeyed. In most cases it will come back the way you'd expect, but it is quite possible that there are certain scenarios in which it will be arbitrary or, otherwise, not what you expected.)
Now, that's great if you're only concerned with getting one list back. But let's say we have a table of athlete names, events and results. For each athlete, we want a row containing the full name and a comma-separated list of events they medaled in. In this case, we'll use a user-defined function to handle the concatenation for us, and a subquery to limit the output to one row per name.
Again, you will not be able to dictate the ordering of the result in each row; this will be decided by the optimizer at runtime, and will not necessarily stay consistent between executions.
For some more thorough examples, please see this thread starring Anith Sen.
SQL Server 2005 adds some interesting options for this kind of non-relational query. You should be able to have more granular control over concatenation with user-defined aggregates, and you can also take advantage of the new FOR XML PATH:
Another way is to use the new CROSS APPLY operator:
Related ArticlesCan I fix this mm/dd/yyyy <-> dd/mm/yyyy confusion once and for all?
Could I get some help with JOINs?
How can I tell which version of MDAC I'm running?
How do I access MIN, MAX, SUM, COUNT values from SQL statements?
How do I change column order in a table structure?
How do I change the order of columns in a table?
How do I convert columns of values into a single list?
How do I determine if a database exists?
How do I document / compare my SQL Server database(s)?
How do I get the IDENTITY / AUTONUMBER value for the row I inserted?
How do I solve 'ADO Could Not Find The Specified Provider'?
Should I use BETWEEN in my database queries?
Why can't I use the * wildcard in a database search?
Why do I get 'Syntax Error in INSERT INTO Statement' with Access?
Why do I get weird results when using both AND and OR in a query?
Why do some SQL strings have an 'N' prefix?
Why does AbsolutePosition return as -1?
Why doesn't SQL Server allow me to separate DATE and TIME?
Why is Query Analyzer only returning 255 characters?
Why should I avoid NULLs in my database?
How do I deal with an apostrophe (') in a SQL statement?