group by a string filed (concatenate the string field)

Here is my PL/SQL query

select tbl1.col1, tbl2.col2, tbl3.col3
from tbl1, tbl2, tbl3
where tbl1.col1 = tbl2.col1
and tbl2.col2 = tbl3.col2
group by
tbl1.col1, tbl2.col2, tbl3.col3

here is the output

col1             col2                 col3
1                 123                  val1
1                 123                  val2
2                 234                  val3
2                 234                  val4
2                 234                  val5


i want to see the output as

col1             col2                 col3
1                  123                 val1-val2
2                  234                 val3-val4-val5

in other words i want to concatenate the col3 field

hope i was clear with the description..... please help me out

Thanks
LVL 9
Raju SrivatsavayeSoftware EngineerAsked:
Who is Participating?
 
sathyagiriCommented:
create or replace function fn_join
 (
     p_cursor sys_refcursor,
     p_delimiter varchar2 := ','
 ) return varchar2
 is
     l_value   varchar2(32767);
     l_result  varchar2(32767);
 begin
     loop
         fetch p_cursor into l_value;
         exit when p_cursor%notfound;
         if l_result is not null then
             l_result := l_result || p_delimiter;
         end if;
         l_result := l_result || l_value;
     end loop;
     return l_result;
 end fn_join;

Then use

select tbl1.col1, tbl2.col2, fn_join(cursor (select tbl3.col3
from tbl3 where tbl1.col1 = tbl2.col1
and tbl2.col2 = tbl3.col2)) from tbl1,tbl2
0
 
SujithData ArchitectCommented:
select col1, col2, trim('-' from sys_connect_by_path(col3, '-') )
from (
select col1, col2, col3,
row_number() over(partition by col1, col2 order by col3) rn,
count(*) over(partition by col1, col2) cp
from
(
 select tbl1.col1, tbl2.col2, tbl3.col3
 from tbl1, tbl2, tbl3
 where tbl1.col1 = tbl2.col1
 and tbl2.col2 = tbl3.col2
)
)
where rn = cp
start with rn = 1
connect by prior col1 = col1
and prior col2 = col2
and prior rn = rn - 1
/
0
 
GGuzdziolCommented:
It depends on Oracle version You have. In 8i there's no sys_connect_by_path I believe, moreover You cannot define Your own aggregate. Here are some links that describe methods for different dbms's

http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2196162600402
0
 
Computer101Commented:
Forced accept.

Computer101
EE Admin
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.

All Courses

From novice to tech pro — start learning today.