Solved

Concatenate rows into one row - Oracle 8i

Posted on 2006-11-30
9
3,564 Views
Last Modified: 2009-12-16
I have the data like:

Col1            Col2            Col3            Col4            Col5
111            a            b            1            YYY
111            a            b            2            XXX
111            a            b            3            ZZZ
111            c            d            1            YY
111            c            d            2            XX
222            e            f            1            ZZ
222            e            g            1            YZ
333            h            I            1            XY

I want to concatenate multiple rows into one row.  The result should like:

Col1            Col2            Col3            Col5
111            a            b            YYYXXXZZZ
111            a            b            YYXX
222            e            f            ZZ
222            e            g            YZ
333            h            I            XY

Please provide a SQL solution.  Thank you!


0
Comment
Question by:ewang1205
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 27

Assisted Solution

by:sujith80
sujith80 earned 250 total points
ID: 18045850
Since the number of distinct values of col2 and col3 are not known in advance it will be difficult to get this done thru SQL.

You can use a function get this concatenated as:

create or replace function test_func(par1 number, par2 varchar2, par3 varchar3)
return varchar2
is
 l_str varchar2(2000);
begin
 for rec in (select col5 from <table> where col1=par1 and col2=par2 and col3=par3) loop
  l_str := l_str||rec.col5;
 end loop;
 return l_str;
end;
/

select col1, col2,col3,test_func(col1,col2,col3)
from <table>
group by col1, col2,col3
0
 
LVL 14

Accepted Solution

by:
GGuzdziol earned 250 total points
ID: 18045918
0
 

Author Comment

by:ewang1205
ID: 18046003
sujith80,

sorry, I put the wrong result.  The correct result should be:
Col1          Col2          Col3          Col5
111          a                 b          YYYXXXZZZ
111          c                 d          YYXX
222          e                 f           ZZ
222          e                 g          YZ
333          h                 I          XY


Thanks.
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 27

Expert Comment

by:sujith80
ID: 18046145
yes,
in any case you can use the above suggestions.
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 18046554
to perform something complex or aggregation , you better use pl/sql because if there is some modification which is needed at a later time then it will be easy to do it otherwise we might get stuck with the sql which might work correctly for the current case but does not allow modifying it to work for other cases.

http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php

This url is good with sample code examples for your understanding.

Thanks

0
 
LVL 23

Expert Comment

by:paquicuba
ID: 18048657
Try this one:

SELECT COL1,COL2,
COL3,
MAX(REPLACE(SYS_CONNECT_BY_PATH(COL5,','),',')) COL5
FROM TAB2
START WITH COL4 = 1
CONNECT BY PRIOR COL4+1 = COL4 AND PRIOR COL2 <> COL2 AND COL1 = PRIOR COL1
GROUP BY COL1,COL2,COL3
ORDER BY 1,2
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 18071164
I posted the wrong version above, use this one:

SELECT COL1,COL2, COL3,
MAX(REPLACE(SYS_CONNECT_BY_PATH(COL5,','),',')) COL5
FROM TAB2
START WITH COL4 = 1
CONNECT BY PRIOR COL4+1 = COL4 AND PRIOR COL2 = COL2 AND COL1 = PRIOR COL1
GROUP BY COL1,COL2,COL3
ORDER BY 1,2
/

      COL1 C C COL5
---------- - - ---------------
       111 a b YYYXXXZZZ
       111 c d YYXX
       222 e f ZZ
       222 e g YZ
       333 h I XY
0
 
LVL 14

Expert Comment

by:GGuzdziol
ID: 18072498
I'd bet there's no SYS_CONNECT_BY_PATH in 8i...
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 18076813
@GGuzdziol,

You're absolutely right. My bad not reading the whole post...

Cheers!
0

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

828 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