Click here to Skip to main content
15,392,813 members
Articles / Database Development / SQL Server / SQL Server 2008
Tip/Trick
Posted 8 Mar 2012

Stats

256.6K views
18 bookmarked

Concatenate many rows into a single text string using SQL Server 2008

Rate me:
Please Sign up or sign in to vote.
4.96/5 (22 votes)
8 Mar 2012CPOL
Using COALESCE function - concatenate many rows into a single text string output in SQL Server.

The following code is used to concatenate many rows into a single text string with comma separated using SQL Server COALESCE function.

My table format is like this:

Name
RAM
GURU
Sundar
Shyam
Inba
Kalai

I need output like this:

"RAM, GURU, Sundar, Shyam, Inba, Kalai"

I use the following query:

SQL
DECLARE @Names VARCHAR(8000)  
SELECT @Names = COALESCE(@Names + ', ', '') + Name FROM People
SELECT @Names

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Ramanujam Shankar
Web Developer Mahindra Logisoft Business Solution Limited, Chenn
India India
No Biography provided

Comments and Discussions

 
QuestionCOALESCE Query Pin
Member 1160005727-Sep-18 9:20
MemberMember 1160005727-Sep-18 9:20 
QuestionThanks great help.. Extend to same discussion Pin
abdulkalam.m8-Mar-16 3:22
Memberabdulkalam.m8-Mar-16 3:22 
AnswerThank you Pin
aravinth santosh10-May-15 23:01
Memberaravinth santosh10-May-15 23:01 
Generalmy vote is 5 (with stars) Pin
Member 1070385724-Apr-15 15:33
MemberMember 1070385724-Apr-15 15:33 
QuestionThis is mostly... just... genius. Pin
Member 15341609-Oct-14 6:50
MemberMember 15341609-Oct-14 6:50 
QuestionSELECT Query Pin
Kannan Karthikeyan6-Oct-14 10:16
MemberKannan Karthikeyan6-Oct-14 10:16 
QuestionTo avoid the ", " at the beginning of the string Pin
Member 1016856530-Sep-13 23:47
MemberMember 1016856530-Sep-13 23:47 
QuestionConcerned about the use of COALESCE vs a Case Function Pin
dalyteaching23-Aug-13 3:51
Memberdalyteaching23-Aug-13 3:51 
GeneralMy vote of 5 Pin
Sk. Tajbir4-Feb-13 22:31
MemberSk. Tajbir4-Feb-13 22:31 
Generalc# Pin
Member 823748321-Dec-12 9:06
MemberMember 823748321-Dec-12 9:06 
GeneralMy vote of 5 Pin
M.Edmison14-Dec-12 9:53
MemberM.Edmison14-Dec-12 9:53 
QuestionFor both sql server 2005 and 2008 Pin
Sunny Setia13-Mar-12 2:45
MemberSunny Setia13-Mar-12 2:45 
AnswerRe: For both sql server 2005 and 2008 Pin
M.Edmison14-Dec-12 9:58
MemberM.Edmison14-Dec-12 9:58 
Questionbla bla Pin
sajith.mkd11-Mar-12 20:18
Membersajith.mkd11-Mar-12 20:18 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.