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

Question

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).

Answer 1

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

Answer 2

DECLARE @firstnames varchar(max)

SELECT 
    @firstnames = COALESCE(@firstnames + ' ', '') + FirstName 
FROM 
    Users 
WHERE 
    UserId < 10

INSERT INTO OtherTable (OtherColumn) VALUES (@firstNames)