Concatenate column values as single value SQL Server 2008

Question

Post reply

Concatenate column values as single value SQL Server 2008

Trainee SQL

Say Hey Kid

Points: 666
More actions
November 25, 2013 at 11:40 pm

#286587

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 need the below value(concatenated) in my variable
'Value One,Value Two,Value Three,Value Four,Value Five'
Can anyone help me out in doing this?
Thanks in advance.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63421 More actions · Answer 1

https://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

Happy reading!

Sean Lange SSC Guru Points: 286573 More actions · Answer 2

It looks like you are trying to create a comma separated list of values? Check out this article. http://www.sqlservercentral.com/articles/71700/[/url]

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

bob 26661 SSC Journeyman Points: 77 More actions · Answer 3

You are trying to concatenate rows in a column, not columns. there is a difference

$t@r SSCommitted Points: 1751 More actions · Answer 4

Try this

declare @ColumnNameList VARCHAR(MAX)

SELECT @ColumnNameList = COALESCE(@ColumnNameList +',' ,'') + ColumnName

FROM

<<table name>>

select @ColumnNameList

kapil_kk SSC-Insane Points: 21316 More actions · Answer 5

$w@t (11/27/2013)
Try this
declare @ColumnNameList VARCHAR(MAX)
SELECT @ColumnNameList = COALESCE(@ColumnNameList +',' ,'') + ColumnName
FROM
<<table name>>
select @ColumnNameList

+1 🙂

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Dwain Camps SSC Guru Points: 86908 More actions · Answer 6

$w@t (11/27/2013)
Try this
declare @ColumnNameList VARCHAR(MAX)
SELECT @ColumnNameList = COALESCE(@ColumnNameList +',' ,'') + ColumnName
FROM
<<table name>>
select @ColumnNameList

While this works for the specific case described by the OP, indexing on the table might impact the order the rows are concatenated in. I suggest you take a look at the link provided by Sean to Wayne Sheffield's article for a much more generally useful solution.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St