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:
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
SELECT ', ' + Lure FROM ( SELECT DISTINCT Lure FROM #LureNames ) x FOR XML PATH
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('')
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:
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:
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
That is how you create a comma separate list in SQL Server.