Selecting multiple values of other column basing on distinct values of column on same table

Question

I am having a table in oracle my_table like

userid            card_no
-------           -------
 111                 A1
 111                 A5
 112                 A3
 113                 A4
 111                 A6
 112                 A8
 113                 A9

In my JSP page I want to show:

------------------------
user_id   card numbers
-------   --------------
111       A1,A5,A6.
112       A3,A8
113       A4,A9
------------------------

GROUP BY IS NOT GIVING RESULTS .

Answer 1

Starting from Oracle 10g onward you can achieve the desired result by using model clause:

SQL> with t1(userid, card_no) as(
  2   select 111,'A1' from dual union all
  3   select 111,'A5' from dual union all
  4   select 112,'A3' from dual union all
  5   select 113,'A4' from dual union all
  6   select 111,'A6' from dual union all
  7   select 112,'A8' from dual union all
  8   select 113,'A9' from dual
  9  )
 10  select userid
 11       , card_no
 12    from ( select userid
 13                , rtrim(res, ',') as card_no
 14                , rn
 15             from t1
 16            model
 17            partition by (userid)
 18            dimension by (row_number() over(partition by userid 
 19                                            order by card_no) as rn)
 20            measures(card_no, cast(null as varchar2(255)) as res)
 21            rules(
 22               res[any] order by rn desc = card_no[cv()] || ',' || res[cv() + 1]
 23            )
 24         ) s
 25  where s.rn = 1
 26  order by userid
 27  ;

Result:

 USERID  CARD_NO
 ----------------
 111     A1,A5,A6
 112     A3,A8 
 113     A4,A9

SQLFiddle Demo

Find out more about model clause

Moreover, there are plenty of other string aggregation techniques.

Answer 2

You can use the LISTAGG function as of Oracle 11g R2.

e.g.

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

Answer 3

select userid, wm_concat(card_no) from table_name group by userid