How to get the grouping output in PLSQL

Question

I am having two tables:

table1 : test1

desc test1

empcode number(6)
qualification_code number(2)

table2 :test2

desc test2

qualification_code number(2)
qualification_name varchar2(10)

select * from test1

120633 10
120633 20
120633 30

select * from test2

10 BSC
20 MCA
30 MBA

I want to select from two tables and I want to get the output as :

empcode :120633 Qualification : BSC,MCA,MBA

How to get this above output.

PL/SQL is **only** used for stored procedure in Oracle. You seem to want a SQL solution. — , Feb 09 '13 at 16:24
possible duplicate of [Oracle: Combine multiple results in a subquery into a single comma-separated value](http://stackoverflow.com/questions/492563/oracle-combine-multiple-results-in-a-subquery-into-a-single-comma-separated-val) — APC, Feb 09 '13 at 19:44

Dan Bracuk · Answer 1 · 2013-02-09T16:43:50.597

0

When I googled "oracle create list from sql", I found ListAgg. In your case, you would want something like this:

select empcode
, ListAgg(qualification_name, ',') 
within group (order by empcode) qualifications
from test1 join test2 on test1.qualification_code = test2.qualification_code
group by empcode

I didn't know this but I think it's pretty slick.

edited Feb 09 '13 at 16:43

answered Feb 09 '13 at 16:35

Dan Bracuk

20,699
4
26
43

score 0 · Answer 2 · answered Feb 09 '13 at 16:39

If you have Oracle 11 or higher, you can use the LISTAGG function, like this:

select empcode
     , listagg(qualification_name, ',') 
          within group (order by qualification_name) as names
from   test1 
join   test2 
on     test2.qualification_code=test1.qualification_code
group by empcode

score 0 · Answer 3 · answered Feb 10 '13 at 15:02

you can also use COLLECT

select empcode , collect(qualification_name) as qualifications
from   test1 , test2
where  test1.qualification_code = test2.qualification_code
group by empcode

you can also use a user defined collection type - very helpful when using with PL/SQL.

Check this out (very good blog)

How to get the grouping output in PLSQL

3 Answers3