Creating a comma separated list with FOR XML PATH and STUFF in SQL Server

I can’t really think of a story or tip that ties into creating a comma separated list, so I’ll just use one that’s completely unrelated.  It’s fun sharing experiences with loved ones, and fishing is no different.  Many of my recent favorite fishing memories involve fishing with my now-four year old son.  Two summers ago when he was two years old we were catching panfish off the dock at my Grandpa’s cabin in northern Wisconsin.  I was hooking them and letting my son net them with a small butterfly net before unhooking them and letting them go.  I had just hooked another one and was trying to pull it close enough for my son to net when suddenly a large northern pike darted in and completely inhaled the hooked bluegill.  Upon seeing such a large fish my son backed off quickly.  The northern pike wasn’t hooked at all, but it still fought me for about half a minute before spitting out the bluegill.  It was something most people don’t get to see, and it was fun to experience it with my son, who was just getting into fishing.

If you need to create a comma separated list in SQL Server, the best way to do this is to use the FOR XML PATH clause.  This takes a set of data and puts it into a format that can be used in XML.  The syntax can be a bit complicated, so let’s take a closer look.  First I’ll build a temp table and fill it with some values – we’ll use muskie lures:

CREATE TABLE #LureNames
    (
      ID INT IDENTITY(1, 1) ,
      Lure VARCHAR(25)
    )
INSERT  INTO #LureNames
        ( Lure )
VALUES  ( 'Mepps Muskie Killer' ),
        ( 'Harasser' ),
        ( 'Buchertail 700' ),
        ( 'Reef Runner' ),
        ( 'Creek Chub Pike Minnow' ),
        ( 'Grandma' ),
        ( 'Jake' ),
        ( 'Shallow Raider' ),
        ( 'Bulldawg' ),
        ( 'Suick Thriller' ),
        ( 'Believer' ),
        ( 'Swim Whizz' ),
        ( 'Double Cowgirl' ),
        ( 'Medussa' ),
        ( 'Doubledawg' ),
        ( 'Shallow Invader' ),
        ( 'Top Raider' ),
        ( 'Depth Raider' ),
        ( 'Pacemaker' ),
        ( 'Hawg Wobbler' ),
        ( 'Softtail Phantom' )

If we do a simple select from this table, we get what we’d expect:

20150128 comma list simple select

If we want to get a comma separate list, we use this syntax:

SELECT  STUFF(( SELECT  ', ' + Lure
                FROM    ( SELECT DISTINCT
                                    Lure
                          FROM      #LureNames
                        ) x
              FOR
                XML PATH('')
              ), 1, 2, '') AllLures

To understand what this does let’s start from the inside and work our way out. The very inside has a rather simple SELECT DISTINCT:

SELECT DISTINCT
     Lure
FROM      #LureNames

This is going to simply give us each lure name once:
20150128 comma list select distinct In the next step we’re going to add the commas and put in the FOR XML PATH clause.

SELECT  ', ' + Lure
FROM    ( SELECT DISTINCT
                    Lure
          FROM      #LureNames
        ) x
FOR     XML PATH

20150128 comma list simple xml You can see the results are now in an xml format. Since we didn’t specify a column name the entries are simply called <row>. We now have the basic comma separated list, but there are extra characters in there. First off are the <row> xml tags, and on top of that the very first entry starts with a comma and space. To get rid of the first issue, we’ll just change FOR XML PATH to FOR XML PATH(”). This tells SQL Server to suppress the row tags.

SELECT  ', ' + Lure
FROM    ( SELECT DISTINCT
                    Lure
          FROM      #LureNames
        ) x
FOR     XML PATH('')

20150128 comma list xml no tags Now the only thing to fix is the leading comma and space. To get rid of this we use the STUFF function. The STUFF function takes a string and deletes a set amount of characters from a specified position and inserts new characters there. For a simple example if I want to change fisherman to fisherwoman, I would go like this:

SELECT  STUFF('fisherman', 7, 0, 'wo')

So I took my initial string, went to the seventh character (just after the ‘r’), deleted 0 characters and inserted ‘wo’ in that location:
20150128 comma list simple stuff For our example above, we’re going to start at the first position and delete two characters, and replace them with an empty string. Now we have our comma separated list:

20150128 comma list with space

It’s worth noting that I put a space between my words in this example.  If we didn’t want the space, we’d change the lurename and comma concatenation to exclude the space, and also only delete the leading one character with the stuff function.

SELECT  STUFF(( SELECT  ',' + Lure
                FROM    ( SELECT DISTINCT
                                    Lure
                          FROM      #LureNames
                        ) x
              FOR
                XML PATH('')
              ), 1, 1, '') AllLures

20150128 comma list no space

That is how you create a comma separate list in SQL Server.

Advertisements

3 thoughts on “Creating a comma separated list with FOR XML PATH and STUFF in SQL Server

  1. Pingback: Creating a comma separated list with ListAgg in Oracle | The Fisherman DBA

  2. Howdy! I basically would like to give a huge thumbs up for the good data you’ve got here on this post. I will probably be coming once again to your weblog for far more soon. ddecgfedbkff

    Like

  3. Pingback: Script to Restore Latest Full Backup with Move Files | The Fisherman DBA

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s