Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Concat multi row values of a field in oracle

Posted on 2016-11-21
6
Medium Priority
?
112 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

730 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