Table Structure:
Desired output:
| ITEMNAME | COLORSOFFERED |
| Shirt | Brown, Blue, Green |
| Pants | Green, Brown |
| Dress | Brown, Blue, Pink, Green |
| Shorts | Green, Brown, Blue |
Have you ran into a situation where you want to combine several rows of data from a sub query in a concatenated string while querying another table? It seems to come up for me a lot, but I’ve historically addressed it in code while using Oracle. I found a way using Oracle functions to do this fairly easily and thought I would share. If you’re running Oracle 11g release 2 I’ve been told there is a LISTAGG function that would replace what I have done here, but this will work for the rest of us.
First, you have to create a function for the sub query:
CREATE OR REPLACE FUNCTION "GET_COLORS" (ITEM_ID VARCHAR2)
RETURN VARCHAR2
IS
color_string VARCHAR2(2500) := NULL;
BEGIN
FOR cur_rec IN (SELECT C.COLORNAME FROM COLOR C, ITEMCOLOR IC WHERE C.COLORID = IC.COLORID AND IC.ITEMID = ITEM_ID) LOOP
color_string := color_string || ', ' || cur_rec.COLORNAME;
END LOOP;
RETURN LTRIM(color_string, ',');
END;
Once the function is created, compliled, and execute permissions granted, etc. you’ll be able to call the function like so :
SELECT ITEMNAME, GET_COLORS(ITEMNAME.ITEMID) AS COLORSOFFERED FROM ITEM
That’s it… Granted this could be tedious if you needed to do this often, but it is a fast solution