Solved

Concat multi row values of a field in oracle

Posted on 2016-11-21
6
64 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 34

Assisted Solution

by:johnsone
johnsone earned 250 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 73

Accepted Solution

by:
sdstuber earned 250 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 73

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

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 73

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Encryption Decryption in Oracle 12 123
Getting TNS:Connect timeout occurred while opening the application 5 80
history tablespace temp usage 2 42
Oracle query output question 4 43
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

803 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