add a comma (,) in Oracle

Question

Given this query:

select distinct subject_key
from mytable

Result:

subject_key
-----------
90896959
90895823
90690171
90669265
90671321

How do i write a query in Oracle (using Aqua Data Studio backend Oracle 8i) result:

subject_key
-----------
90896959,
90895823,
90690171,
90669265,
90671321

THANKS ALL! Should I wish to change the output across instead of down like below. How do I write it, same platform. Thanks.

subject_key
90896959,  90895823, 90690171,  90669265, 90671321

Answer 1

Oracle doesn't have a function like MySQL's GROUP_CONCAT, which is exactly the functionality you're asking for. Various options for such string aggregation are provided on this page - one is to use a custom function:

CREATE OR REPLACE FUNCTION get_subjectkey (IN_PK IN MYTABLE.PRIMARY_KEY%TYPE)
RETURN VARCHAR2
IS
  l_text  VARCHAR2(32767) := NULL;
BEGIN

  FOR cur_rec IN (SELECT subject_key 
                    FROM MYTABLE 
                   WHERE primary_key = IN_PK) LOOP
    l_text := l_text || ',' || cur_rec.ename;
  END LOOP;

  RETURN LTRIM(l_text, ',');
END;

Then you'd use it like:

SELECT get_subjectkey(?) AS subject_key
  FROM DUAL

...replacing the "?" with the primary key value.

Previously

Assuming you just want to add a comma to the end of the column value, use:

SELECT DISTINCT TO_CHAR(subject_key) || ','
  FROM MYTABLE

The double pipe -- "||" -- is the Oracle [,PostgreSQL and now ANSI] means of concatenating strings in SQL. I used TO_CHAR to explicitly convert the data type, but you could use:

SELECT DISTINCT subject_key || ','
  FROM MYTABLE

...if that's not necessary.