How do I combine a column from multiple rows into a string or new column?

Question

Post reply

How do I combine a column from multiple rows into a string or new column?

SQLConfused

Grasshopper

Points: 18
More actions
August 15, 2011 at 11:06 am

#255774

COLUMNA COLUMNB
123 JSOSJD
433 SDFOJE
123 DODOD
123 399SD
"JSOSJD,DODOD,399SD" where columnA=123

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

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Try the stuff function:

Select STUFF(

(SELECT ',' + a.columnA AS [text()]

from yourtable a

for xml PATH('')),1,1,'' )

Where columnA=123

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLConfused Grasshopper Points: 18 More actions · Answer 2

Thanks. That gave me all rows in one so I changed the where clause and now it is accurate.

Select STUFF(

(SELECT ',' + a.columnA AS [text()]

from yourtable a Where columnA=123

for xml PATH('')),1,1,'' )

--Where columnA=123

--------------------------------------------------------------------------------

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Glad it is working.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Tee Time Hall of Fame Points: 3745 More actions · Answer 4

Very nice! I will definitely find the STUFF fuction useful. Learning more and more each day thanks to you all! 🙂

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

Tee Time (8/29/2011)
Very nice! I will definitely find the STUFF fuction useful. Learning more and more each day thanks to you all! 🙂

You do realize that all the STUFF function did was eliminate a leading comma, right? And that the FOR XML PATH did the rest, right? 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)