Click here to Skip to main content
13,139,569 members (55,068 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

76.4K views
24 bookmarked
Posted 16 Oct 2014

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

, 16 Oct 2014
Rate this:
Please Sign up or sign in to vote.
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

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;

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

You may also be interested in...

Pro
Pro

Comments and Discussions

 
QuestionThat Works But How Pin
Maulik_Modi23-Nov-15 17:58
memberMaulik_Modi23-Nov-15 17:58 
GeneralMy vote of 4 Pin
Suvendu Shekhar Giri5-Nov-15 18:05
professionalSuvendu Shekhar Giri5-Nov-15 18:05 
QuestionMessage Closed Pin
17-Oct-14 7:14
memberMember 1116166517-Oct-14 7:14 
AnswerHow to get SQL column values as comma separated string using XML path method Pin
8838813217-Oct-14 2:09
professional8838813217-Oct-14 2:09 
GeneralMy vote of 1 Pin
8838813217-Oct-14 1:00
professional8838813217-Oct-14 1:00 
GeneralRe: My vote of 1 Pin
Matt Tomalin17-Oct-14 1:11
memberMatt Tomalin17-Oct-14 1:11 
GeneralRe: My vote of 1 Pin
banitabisht17-Oct-14 1:13
memberbanitabisht17-Oct-14 1: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.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.170915.1 | Last Updated 17 Oct 2014
Article Copyright 2014 by banitabisht
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid