Concat multi row values of a field in oracle

I want to concat multi row values of a given field in oracle. For a simple illustration, say, multiple grades of a studentID field

Table
StuID  Subject Grade
1      MATH      60
1      SCI      50
2      MATH      80
2      SCI      90
3       MATH      78

I would like to return a resultset like:

StuID Grades
1      60;50
2      80;90
3      78

Please help me figure out how to do that.
LuckyLucksAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
sdstuberConnect With a Mentor Commented:
Do you have 11gR2 or higher?

select stuid, listagg(grade,';') within group(order by subject)
from yourtable
group by stuid
order by stuid
0
 
johnsoneConnect With a Mentor Senior Oracle DBACommented:
If your version supports it, use LISTAGG.  The example in the documentation is almost exactly what you want.

http://docs.oracle.com/database/122/SQLRF/LISTAGG.htm#SQLRF30030
0
 
sdstuberCommented:
if you don't have 11gR2, but have 9i or higher you can try creating your own aggregate

https://www.experts-exchange.com/articles/9391/How-to-Create-User-Defined-Aggregates-in-Oracle.html

select stuid,concatagg(grade)
from yourtable
group by stuid
order by stuid


congatagg is the basically the same as stragg found in other links on EE and outside of EE.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
sdstuberCommented:
you could also try XML aggregation - it's not as efficient as either of the above, but doesnt require 11gR2 and doesn't require creating extra objects


select stuid, rtrim(xmlquery('//x/text()' passing XMLAGG(XMLELEMENT("x", grade||';')) returning content),';')
from yourtable
group by stuid
order by stuid



it's also possible to do string aggregation with CONNECT BY queries, but I highly recommend pursuing some other method.
Using connect by is extremely inefficient compared to any of the above suggestions
0
 
Adnan SaleemApplication Analyst - Oracle EBSCommented:
Hello,

Please try this;

SELECT X.*,
       (select rtrim(xmlagg(xmlelement(e,empno || ' - ' || ename || '; ' )).extract  ( '//text()' ), ',') from emp y where y.mgr=x.empno) employees
FROM EMP X
WHERE MGR is null


Thank you
0
 
sdstuberCommented:
Adnan Saleem, please read prior posts before adding to make sure you're not posting a duplicate.

Your xmlagg is effectively the same method that was already posted a week before.  The only significant difference was yours uses "extract" which has been deprecated.

xmlquery is the recommended method for parsing items out of an xmltype
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.