I need to return multiple rows into one single string

• sai.mopidevi

  SSC Veteran

  Points: 290

  More actions

  October 15, 2010 at 1:07 am

  #97507

  Select * from Cus returns

  1John

  2 Bob

  I need to return the all the rows from Cus table into a single string. The return is dynamic.

  I do not know the number of rows returned

  My result should be

  @String = 1,John,2,Bob

• WayneS

  SSC Guru

  Points: 95461

  More actions

  October 15, 2010 at 7:26 am

  #1237519

  DECLARE @cus TABLE (col1 int, col2 varchar(50));

  INSERT INTO @cus

  SELECT 1, 'John' UNION ALL

  SELECT 2, 'Bob';

  SELECT stuff((SELECT ',' + convert(varchar(10), col1) + ',' + col2

  FROM @cus

  ORDER BY col1

  FOR XML PATH(''),TYPE).value('.','varchar(max)'),1,1,'')

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2