• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3582
  • Last Modified:

Concatenate rows into one row - Oracle 8i

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
ewang1205
Asked:
ewang1205
  • 3
  • 2
  • 2
  • +2
2 Solutions
 
SujithData ArchitectCommented:
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
 
ewang1205Author Commented:
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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
SujithData ArchitectCommented:
yes,
in any case you can use the above suggestions.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
paquicubaCommented:
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
 
paquicubaCommented:
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
 
GGuzdziolCommented:
I'd bet there's no SYS_CONNECT_BY_PATH in 8i...
0
 
paquicubaCommented:
@GGuzdziol,

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

Cheers!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now