SQL Query – Concatenating Results into One String [duplicate]

by

If you’re on SQL Server 2005 or up, you can use this FOR XML PATH & STUFF trick:

DECLARE @CodeNameString varchar(100)

SELECT 
   @CodeNameString = STUFF( (SELECT ',' + CodeName 
                             FROM dbo.AccountCodes 
                             ORDER BY Sort
                             FOR XML PATH('')), 
                            1, 1, '')

The FOR XML PATH('') basically concatenates your strings together into one, long XML result (something like ,code1,code2,code3 etc.) and the STUFF puts a “nothing” character at the first character, e.g. wipes out the “superfluous” first comma, to give you the result you’re probably looking for.

UPDATE: OK – I understand the comments – if your text in the database table already contains characters like <, > or &, then my current solution will in fact encode those into &lt;, &gt;, and &amp;.

If you have a problem with that XML encoding – then yes, you must look at the solution proposed by @KM which works for those characters, too. One word of warning from me: this approach is a lot more resource and processing intensive – just so you know.

More Related Contents:

  1. Function vs. Stored Procedure in SQL Server
  2. Why do SQL Server Scalar-valued functions get slower?
  3. Is it possible to write single query on two table which are not connected to each other?
  4. How to create a specific SQL Server stored procedure? [closed]
  5. SQL Server – Best way to get identity of inserted row?
  6. Should I use != or for not equal in T-SQL?
  7. How to update two tables in one statement in SQL Server 2005?
  8. Create a date from day month and year with T-SQL
  9. How should I pass a table name into a stored proc?
  10. Is the NOLOCK (Sql Server hint) bad practice?
  11. Creating an index on a table variable
  12. OR is not supported with CASE Statement in SQL Server
  13. Parse comma-separated string to make IN List of strings in the Where clause
  14. SELECT FOR UPDATE with SQL Server
  15. Compare DATETIME and DATE ignoring time portion
  16. T-SQL: Selecting Column Based on MAX(Other Column)
  17. How to group by month from Date field using sql
  18. unresolved reference to object [INFORMATION_SCHEMA].[TABLES]
  19. SQL dot notation
  20. Curly braces in T-SQL
  21. OPENXML with xmlns:dt
  22. SQL Server replace, remove all after certain character
  23. How to find column names for all tables in all databases in SQL Server
  24. Inserting rows into a table with one IDENTITY column only [duplicate]
  25. SQL Row_Number() function in Where Clause
  26. When using GETDATE() in many places, is it better to use a variable?
  27. How do I compare 2 rows from the same table (SQL Server)?
  28. SQL – How do I get only the numbers after the decimal?
  29. How to retrieve records for last 30 minutes in MS SQL?
  30. Group consecutive rows of same value using time spans

Leave a Comment