Quick way to get column into a list string?

• vince.iacoboni@db.com

  Hall of Fame

  Points: 3965

  More actions

  March 10, 2003 at 7:24 am

  #79781

  I'm looking for a quick way to get a column's values into a delimited varchar string. If stored procedures or functions would permit table variables as parameters, this wouldn't be an issue. Since they don't, I sometimes create functions that accept a delimited list as a varchar parm, and I have a split function to turn that into a column of rows. But going the other way (the equivalent of perl's join function) is harder.

  Ways to do it so far:

  1. Looping or cursoring over set, like below (assumes unique column).

  DECLARE @MyTable TABLE (MyCol int)

  DECLARE @List varchar(1000), @Col int

  -- Fill @MyTable

  SELECT @Col = min(MyCol) FROM @MyTable

  SET @List = convert(varchar(10), @Col)

  WHILE EXISTS (SELECT * FROM @MyTable WHERE Col > @Col)

  BEGIN

  SELECT @Col = min(MyCol) FROM @MyTable WHERE Col > @Col

  SET @List = @List + ',' + convert(varchar(10), @Col)

  END

  2. Filling a temp table that a stored proc expects to read:

  INSERT #ListTable SELECT MyCol FROM @MyTable

  EXEC DelimitColumn @List OUTPUT

  --DelimitColumn SP:

  CREATE PROC DelimitColumn @List varchar(4000) OUTPUT

  AS

  -- do as above in #1

  Are there any other ideas out there?

  Vince

• Chris Hedgate

  One Orange Chip

  Points: 25041

  More actions

  March 10, 2003 at 8:43 am

  #450688

  DECLARE @Foo varchar(4000)

  SET @Foo = ''

  SELECT @Foo = @Foo + CONVERT(varchar(10), mycol) + ',' FROM mytable

  PRINT @Foo

  --

  Chris Hedgate @ Apptus Technologies (http://www.apptus.se)

  http://www.sql.nu

  --
  Chris Hedgate http://www.hedgate.net/
  Contributor to the Best of SQL Server Central volumes
  Articles: http://www.sqlservercentral.com/columnists/chedgate/

• Greg Larsen

  SSC-Insane

  Points: 20961

  More actions

  March 10, 2003 at 9:32 am

  #450697

  Here is the same basic example, but this one removes the last trailing comma.

  set nocount on

  create table x(a char(1), B CHAR(1))

  INSERT INTO X values('a','z')

  insert into x values('b','y')

  insert into x values('c','x')

  declare @x char(100)

  declare @sep char(2)

  set @x = ''

  set @sep = ''

  select @x = rtrim(@x) + rtrim(@sep) + a, @sep = ', ' from x

  print 'Values for column a are: ' + @x

  drop table x

  Gregory Larsen, DBA

  If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

  Gregory A. Larsen, MVP