add a comma (,) in Oracle

oracle, oracle8i, sql, string

Solution

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.

Problem

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 ```

Original source