Concatenate column values as single value SQL Server 2005

Question

I have a table with a column:

|-------------|
| ColumnName  |
|-------------|
| Value One   |
| Value Two   |
| Value Three |
| Value Four  |
| Value Five  |
|-------------|

I will declare a variable,

DECLARE @ColumnNameList VARCHAR(MAX)

and I should find the below value in my variable

'Value One,Value Two,Value Three,Value Four,Value Five'

Can anyone help me out in doing this?

Thanks in advance.

Answer 1

SELECT @ColumnNameList = STUFF 
(
    SELECT ',' + ColumnName  
    FROM TableName
    ORDER BY ColumnName  
    FOR XML PATH('root')
), 1, 1, ''