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.

possible duplicate of [How to concatenate all strings from a certain column for each group](http://stackoverflow.com/questions/6579440/how-to-concatenate-all-strings-from-a-certain-column-for-each-group) — Satpal, Aug 26 '13 at 09:09

score 5 · Answer 1 · answered Aug 26 '13 at 09:09

5

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

answered Aug 26 '13 at 09:09

Giannis Paraskevopoulos

18,261
1
49
69

score 2 · Answer 2 · answered Aug 26 '13 at 09:11

2

SELECT @ColumnNameList = coalesce(@ColumnNameList + ',' + ColumnName, ColumnName) 
FROM <table>
SELECT @ColumnNameList

answered Aug 26 '13 at 09:11

t-clausen.dk

43,517
12
59
92

score -1 · Answer 3 · edited Jan 13 '15 at 11:23

Data in dbo.columnnames is: columnnames Value One Value Two Value Three Value Four Value Five

CODE:

declare @lv_table table( column_values varchar(100))

declare @product_v varchar(max)

insert into @lv_table

select * from dbo.columnnames


select ''''+
    Stuff(
        (
    SELECT  ',' + column_values 
    FROM  @lv_table
    FOR XML PATH('')
    ), 1, 1, '') +''''

output:

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

Concatenate column values as single value SQL Server 2005

3 Answers3