I want the output without using the case or decode in Oracle 10g

Question

 STUD_ID SUBJECT              GRADE

 1       math                 a
 1       english              b
 1       computer             c
 2       math                 c
 2       lang                 a
 3       phys                 a
 3       chem                 b

Output should be like this:

  stud_id   grades
    1       a,b,c
    2       c,a
    3       a,b

Answer 1

If you are on Oracle version 11g and up, you could simply use LISTAGG string aggregation function.

For example,

*SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM   emp
GROUP BY deptno;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

3 rows selected.

For other version specific solution, please see the examples demonstrated here.

NOTE Remember, DO NOT use wm_concat, it was undocumented in prior 12c versions, and it is not supported in 12c. See my answer here for more details.

UPDATE On OP's request regarding solution for 10g.

There are two ways as mentioned in Tim Hall's article:

• COLLECT function in Oracle 10g

For example,

CREATE OR REPLACE TYPE t_varchar2_tab AS TABLE OF VARCHAR2(4000);
/

CREATE OR REPLACE FUNCTION tab_to_string (p_varchar2_tab  IN  t_varchar2_tab,
                                          p_delimiter     IN  VARCHAR2 DEFAULT ',') RETURN VARCHAR2 IS
  l_string     VARCHAR2(32767);
BEGIN
  FOR i IN p_varchar2_tab.FIRST .. p_varchar2_tab.LAST LOOP
    IF i != p_varchar2_tab.FIRST THEN
      l_string := l_string || p_delimiter;
    END IF;
    l_string := l_string || p_varchar2_tab(i);
  END LOOP;
  RETURN l_string;
END tab_to_string;
/

• ROW_NUMBER() and SYS_CONNECT_BY_PATH functions in Oracle 9i and up

For example,

SELECT deptno,
       LTRIM(MAX(SYS_CONNECT_BY_PATH(ename,','))
       KEEP (DENSE_RANK LAST ORDER BY curr),',') AS employees
FROM   (SELECT deptno,
               ename,
               ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS curr,
               ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) -1 AS prev
        FROM   emp)
GROUP BY deptno
CONNECT BY prev = PRIOR curr AND deptno = PRIOR deptno
START WITH curr = 1;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

3 rows selected.