Add comma (,) to Oracle

Question

Given this request:

select distinct subject_key
from mytable

Result:

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

How to write a query in Oracle (using the Oracle 8i backend from Aqua Data Studio):

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

THANK YOU ALL! Should I change the output, not down, as shown below. How can I write, on the same platform. Thank.

subject_key
90896959,  90895823, 90690171,  90669265, 90671321

Answer 1

Oracle does not have a function like MySQL GROUP_CONCAT, and these are exactly the functions you are asking for. This page presents various options for such row aggregation - you need 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 will use it like:

SELECT get_subjectkey(?) AS subject_key
  FROM DUAL

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

Earlier

, , :

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

- "||" - Oracle [, PostgreSQL ANSI] SQL. TO_CHAR , :

SELECT DISTINCT subject_key || ','
  FROM MYTABLE

... .

Answer 2

: SELECT subject_key + ',' AS subject_key FROM mytable

, T-SQL. PL-SQL .