Click here to Skip to main content
14,691,972 members
Articles » Database » Database » SQL Server
Tip/Trick
Posted 16 Oct 2014

Tagged as

Stats

139.9K views
25 bookmarked

How to Get SQL Column Values as Comma Separated String using XML Path Method

Rate me:
Please Sign up or sign in to vote.
4.67/5 (13 votes)
16 Oct 2014CPOL
Tip to get comma separated string for a column in SQL query using XML path method

Introduction

There are many scenarios where we have to show the values of column value as comma (or any other special character) separated string in result set.

This can be easily achieved using XML path method in SQL.

Using the SQL Query

Let's take an example of ProductDetails Table:

SELECT *  FROM  ProductDetails

Image 1

Now suppose we want to fetch the list of products along with available colors where multiple colors are shown as comma separated string.

SELECT DISTINCT p.ProductName,
  STUFF((SELECT distinct ',' + p1.[Color]
         FROM ProductDetails p1
         WHERE p.ProductName = p1.ProductName
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'') Color
FROM ProductDetails p;

Image 2

So in this way, we can fetch column value with comma separated string.

License

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

Share

About the Author

banitabisht
Software Developer
India India
No Biography provided

Comments and Discussions

 
GeneralFeedback Pin
Devesh Kumar Singh20-Oct-19 1:29
MemberDevesh Kumar Singh20-Oct-19 1:29 
QuestionGetting error when executing.. Pin
Member 1383705621-May-18 2:14
MemberMember 1383705621-May-18 2:14 
QuestionThat Works But How Pin
Maulik_Modi23-Nov-15 18:58
MemberMaulik_Modi23-Nov-15 18:58 
GeneralMy vote of 4 Pin
Suvendu Shekhar Giri5-Nov-15 19:05
professionalSuvendu Shekhar Giri5-Nov-15 19:05 
Useful one Smile | :)
AnswerHow to get SQL column values as comma separated string using XML path method Pin
Sid_Joshi17-Oct-14 3:09
professionalSid_Joshi17-Oct-14 3:09 
GeneralMy vote of 1 Pin
Sid_Joshi17-Oct-14 2:00
professionalSid_Joshi17-Oct-14 2:00 
GeneralRe: My vote of 1 Pin
Matt Tomalin17-Oct-14 2:11
MemberMatt Tomalin17-Oct-14 2:11 
GeneralRe: My vote of 1 Pin
banitabisht17-Oct-14 2:13
Memberbanitabisht17-Oct-14 2:13 

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.