Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Concat multi row values of a field in oracle

Posted on 2016-11-21
6
Medium Priority
?
115 Views
Last Modified: 2016-12-01
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
Comment
Question by:LuckyLucks
  • 4
6 Comments
 
LVL 35

Assisted Solution

by:johnsone
johnsone earned 1000 total points
ID: 41896493
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
 
LVL 74

Accepted Solution

by:
sdstuber earned 1000 total points
ID: 41896494
Do you have 11gR2 or higher?

select stuid, listagg(grade,';') within group(order by subject)
from yourtable
group by stuid
order by stuid
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 41896496
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 74

Expert Comment

by:sdstuber
ID: 41896501
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
 

Expert Comment

by:Adnan Saleem
ID: 41908278
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 41908717
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question