select from table, then form a space-separated string all in a stored procedure

Hi,

I want to select about 4-5 rows from a table, then form a space separated string.

All of this is to be done in a stored procedure (SQL server 2005).

Is this possible?

I will then use this space-separated string and save it to another table.

Update

SELECT *
FROM Users
WHERE userID < 10

output:

john
jake
blah
sam

So, put this in a space separated string:

'john jake blah sam'

and save that string to another row in a table.

All this has to be done in a stored procedure (if possible).

From stackoverflow

• I think something like this will work:

  DECLARE @whatever varchar(max)  -- or varchar(1000) or whatever size
  SET @whatever = ''
  
  SELECT @whatever = @whatever + MyColumn + ' ' FROM MyTable
  

• DECLARE @firstnames varchar(max)
  
  SELECT 
      @firstnames = COALESCE(@firstnames + ' ', '') + FirstName 
  FROM 
      Users 
  WHERE 
      UserId < 10
  
  INSERT INTO OtherTable (OtherColumn) VALUES (@firstNames)
  

  Ian Nelson : It's magic :-) Care to accept my answer?