group by a string filed (concatenate the string field)

Posted on 2007-07-26
Last Modified: 2012-05-05
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

Question by:srivatsavaye
    LVL 9

    Accepted Solution

    LVL 14

    Assisted Solution

    create or replace function fn_join
         p_cursor sys_refcursor,
         p_delimiter varchar2 := ','
     ) return varchar2
         l_value   varchar2(32767);
         l_result  varchar2(32767);
             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
    LVL 27

    Assisted Solution

    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
     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
    LVL 14

    Assisted Solution

    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
    LVL 1

    Expert Comment

    Forced accept.

    EE Admin

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
    Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
    This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
    This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

    746 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now