[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 120
  • Last Modified:

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.
0
LuckyLucks
Asked:
LuckyLucks
  • 4
2 Solutions
 
johnsoneSenior 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:
Do you have 11gR2 or higher?

select stuid, listagg(grade,';') within group(order by subject)
from yourtable
group by stuid
order by stuid
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now