SQL Query - Concatenating Results into One String

Question

I have a sql function that includes this code   DECLARE  CodeNameString varchar 100   SELECT CodeName FROM AccountCodes ORDER BY Sort   I need to concatenate all results from the select query into CodeNameString   Obviously a FOREACH loop in C  code would do this  but how do I do it in SQL

User · Answer

For SQL Server 2005 and above use Coalesce for nulls and I am using Cast or Convert  if there are numeric values -   declare  CodeNameString  nvarchar max  select   CodeNameString   COALESCE  CodeNameString               Cast CodeName as varchar  from AccountCodes  ORDER BY Sort select   CodeNameString

User · Answer

DECLARE  CodeNameString varchar max  SET  CodeNameString     SELECT  CodeNameString  CodeNameString CodeName FROM AccountCodes ORDER BY Sort SELECT  CodeNameString

User · Answer

Here is another real life example that works fine at least with 2008 release  and later    This is the original query which uses simple max   to get at least one of the values   SELECT option name  Field M3 name  max Option value  AS  Option value   max Sorting  AS  Sorted  FROM Value list group by Option name  Field M3 name ORDER BY option name  Field M3 name   Improved version  where the main improvement is that we show all values comma separated   SELECT from1 keys  from1 option name  from1 Field M3 name    Stuff  SELECT DISTINCT         Option value  FROM Value list from2   WHERE COALESCE from2 Option name             COALESCE from2 Field M3 name       from1 keys FOR XML PATH     TYPE     value  text   1    nvarchar max    1 2 N    AS  Option values     Stuff  SELECT DISTINCT        CAST  Sorting  AS VARCHAR  FROM Value list from2   WHERE COALESCE from2 Option name             COALESCE from2 Field M3 name       from1 keys FOR XML PATH     TYPE     value  text   1    nvarchar max    1 2 N    AS  Sorting   FROM   SELECT DISTINCT COALESCE Option name             COALESCE Field M3 name     AS keys  Option name  Field M3 name FROM Value list  -- WHERE   from1 ORDER BY keys   Note that we have solved all possible NULL case issues that I can think of and also we fixed an error that we got for numeric values  field Sorting

User · Answer

If you re on SQL Server 2005 or up  you can use this FOR XML PATH  amp  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  lt    gt  or  amp   then my current solution will in fact encode those into  amp lt    amp gt   and  amp 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

User · Answer

from msdn Do not use a variable in a SELECT statement to concatenate values  that is  to compute aggregate values   Unexpected query results may occur  This is because all expressions in the SELECT list  including assignments  are not guaranteed to be executed exactly once for each output row  The above seems to say that concatenation as done above is not valid as the assignment might be done more times than there are rows returned by the select

User · Answer

AlexanderMP s answer is correct  but you can also consider handling nulls with coalesce   declare  CodeNameString  nvarchar max  set  CodeNameString   null SELECT  CodeNameString   Coalesce  CodeNameString               cast CodeName as varchar  from AccountCodes   select  CodeNameString

[sql] SQL Query - Concatenating Results into One String

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to sql-function