oracle LEFT JOIN comma separated string

Question

I have 3 tables:

RESULTS

ID NUMBER
TEXT VARCHAR
URL VARCHAR

KEYWORDS

ID NUMBER
KEYWORD VARCHAR

KEYWORD_RESULT

KEYWORD_ID NUMBER
RESULT_ID NUMBER

I want to select results with comma separated keywords. Result should look something like this:

ID  TEXT       URL           keywords
1   some text  www.some.com  keyword1, keyword2, keyword3

How could I select such a result?

What version of Oracle do you have? – Mark Leiber May 19 '15 at 18:15 — Mark Leiber, May 19 '15 at 18:15

score 3 · Answer 1 · edited May 23 '17 at 12:14

If you have Oracle 11g Release 2, you can use the LISTAGG function: http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm#SQLRF30030

select r.id, r.text, r.url
, listagg(k.keyword, ',') within group (order by k.keyword) as keywords
from results r
left join keyword_result kr on r.id = kr.result_id
left join keywords k on k.id = kr.keyword_id
group by r.id, r.text, r.url
order by r.id;

If you have a lower Oracle version, you'll have to define your own function to generate the comma-delimited list, as shown here: Is there any function in oracle similar to group_concat in mysql? (see the get_comma_separated_value function). It would look like this:

CREATE OR REPLACE FUNCTION get_comma_separated_value (input_val  in  number)
  RETURN VARCHAR2
IS
  return_text  VARCHAR2(10000) := NULL;
BEGIN
  FOR x IN (SELECT k.keyword FROM keyword_result kr 
            join keywords k on kr.keyword_id = k.id 
            WHERE kr.result_id = input_val) LOOP
    return_text := return_text || ',' || x.keyword ;
  END LOOP;
  RETURN LTRIM(return_text, ',');
END;
/

Then the query would look like:

select r.id, r.text, r.url
, get_comma_separated_value(r.id) as keywords
from results r
left join keyword_result kr on r.id = kr.result_id
left join keywords k on k.id = kr.keyword_id
group by r.id, r.text, r.url
order by r.id;

oracle LEFT JOIN comma separated string

1 Answers1