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

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