Add A Comma (,) In Oracle

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

Solution 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:

CREATEOR REPLACE FUNCTION get_subjectkey (IN_PK IN MYTABLE.PRIMARY_KEY%TYPE)
RETURN VARCHAR2
IS
  l_text  VARCHAR2(32767) :=NULL;
BEGINFOR 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:

SELECTDISTINCT 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:

SELECTDISTINCT subject_key ||','FROM MYTABLE

...if that's not necessary.

Solution 2:

most likely: SELECT subject_key + ',' AS subject_key FROM mytable

At least that's how in T-SQL. PL-SQL may be slightly different.