Creating a comma separated list with ListAgg in Oracle

When moving to a new location on a lake to fish I do a mental inventory on my lures to identify which ones would work for that location. First I’ll categorize my lures by type (spinner, topwater, jerkbait, crankbait), then I’ll think about which lures in each category will work in that location. If there are weeds coming up I don’t want anything that’ll dive too deep and get fouled each cast. If there are no weeds and I’m working bottom structure I want a lure that will get down deep enough, closer to the bottom. If I’m in clear water I want natural colored lures, if I’m in stained water I want brightly colored lures. If the fish have been active I’ll want lures that can be worked fast, so I can cover as much water as possible. If they’ve been passive I’ll choose a lure that can be worked slowly, giving the fish plenty of time to decide to strike. Once my mental inventory is finished, I have a list of lures to choose from that will give me the best chance to catch a fish.
I’ve already demonstrated how to create a comma separated list in SQL Server here. Creating one in Oracle is a bit different, and in my opinion, easier. This method works starting in 11g, which most people are on by now.  To demonstrate, let’s start by creating a set of sample data:

CREATE TABLE DGUGG.LureNames
  (ID NUMBER PRIMARY KEY , Lure VARCHAR2(25), PriceRange VARCHAR2(8));
  
CREATE sequence DGUGG.LureNameID_seq;

CREATE OR REPLACE TRIGGER DGUGG.LureNamesID_trg before
  INSERT ON DGUGG.LureNames FOR EACH row BEGIN
  SELECT LureNameID_seq.nextval INTO :new.ID FROM dual;
END;
/

INSERT ALL
  INTO DGUGG.LureNames ( Lure, PriceRange ) VALUES  ( 'Mepps Muskie Killer', '$10-$15' )
  INTO DGUGG.LureNames ( Lure, PriceRange ) VALUES  ( 'Harasser', '$10-$15' )
  INTO DGUGG.LureNames ( Lure, PriceRange ) VALUES  ( 'Buchertail 700','$10-$15')
  INTO DGUGG.LureNames ( Lure, PriceRange ) VALUES  ( 'Reef Runner', '$15-$20' )
  INTO DGUGG.LureNames ( Lure, PriceRange ) VALUES  ( 'Creek Chub Pike Minnow', '$10-$15' )
  INTO DGUGG.LureNames ( Lure, PriceRange ) VALUES  ( 'Grandma', '$15-$20' )
  INTO DGUGG.LureNames ( Lure, PriceRange ) VALUES  ( 'Jake', '$15-$20' )
  INTO DGUGG.LureNames ( Lure, PriceRange ) VALUES  ( 'Shallow Raider', '$15-$20' )
  INTO DGUGG.LureNames ( Lure, PriceRange ) VALUES  ( 'Bulldawg', '$10-$15' )
  INTO DGUGG.LureNames ( Lure, PriceRange ) VALUES  ( 'Suick Thriller', '$15-$20' )
  INTO DGUGG.LureNames ( Lure, PriceRange ) VALUES  ( 'Believer', '$15-$20' )
  INTO DGUGG.LureNames ( Lure, PriceRange ) VALUES  ( 'Swim Whizz', '$15-$20' )
  INTO DGUGG.LureNames ( Lure, PriceRange ) VALUES  ( 'Double Cowgirl', '$20-$25' )
  INTO DGUGG.LureNames ( Lure, PriceRange ) VALUES  ( 'Medussa', '$20-$25' )
  INTO DGUGG.LureNames ( Lure, PriceRange ) VALUES  ( 'Doubledawg', '$15-$20' )
  INTO DGUGG.LureNames ( Lure, PriceRange ) VALUES  ( 'Shallow Invader', '$15-$20' )
  INTO DGUGG.LureNames ( Lure, PriceRange ) VALUES  ( 'Top Raider', '$15-$20' )
  INTO DGUGG.LureNames ( Lure, PriceRange ) VALUES  ( 'Depth Raider', '$15-$20' )
  INTO DGUGG.LureNames ( Lure, PriceRange ) VALUES  ( 'Pacemaker', '$15-$20' )
  INTO DGUGG.LureNames ( Lure, PriceRange ) VALUES  ( 'Hawg Wobbler', '$25-$30' )
  INTO DGUGG.LureNames ( Lure, PriceRange ) VALUES  ( 'Softtail Phantom', '$25-$30' )
SELECT * FROM dual;

I’ve created a table called LureNames and inserted several lures names into it, as well as an approximate price range. A simple example would get all the lure names into a comma separated list:

SELECT listagg(Lure,', ')WITHIN GROUP (ORDER BY 1) AllLures FROM DGUGG.LureNames;

The Listagg() function takes two arguments, the source of data (the field Lure in this example) and the delimiter. I have made the delimiter a comma and space. After WITHIN GROUP we are able to choose how to order the results within the list.
20150203 Oracle Comma Delimited List simple
A slightly more complex example will group all the lures together into a comma separated list within each price range bucket:

SELECT PriceRange, listagg(Lure,', ') WITHIN GROUP (ORDER BY Lure DESC) LureList 
FROM DGUGG.LureNames 
GROUP BY PriceRange;

This time I added the PriceRange field so I also had to group by it. I also switched the order by Lure to descending so the lists start with the lowest alphabetical name and work up.
20150203 Oracle Comma Delimited List complex
So that is how to create a comma separate list in Oracle.  It is a bit easier since it only requires one function, not a second to clip off the leading delimiter like in SQL Server.  Next post I will be using this functionality to create a database email with an HTML table of query results in Oracle.

Advertisements

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.