This discussion is archived
9 Replies Latest reply: Nov 26, 2012 12:19 AM by BluShadow RSS

concat multiple output lines

975565 Newbie
Currently Being Moderated
Hi all,

I'm struggling with a problem for quite a while now and can't find any solution online so far ...

OK I got three different Tables



cust_tbl:

customer:id

nuxx :1

naxx :2

baxx :3

suxx :4



parts_tbl:

part_nr : amount : id

x201 : 100 : 11

ds32 : 100 : 12

ddd11 : 200 : 13

lkj1 : 250 : 14



order_tbl:



cust_id : part_id : address

1 : 11 : aaa

1 : 11 : bbb

1 : 12 : ccc

2 : 11 : ddd

2 : 14 : eee

3 : 13 : fff

4 : 12 : ggg



Now, what I need is an output like this:

Customer, part1, amount1, address1,part2, amount2, addess2, ...

e.g.

nuxx:x201-100-aaa,x201-100-bbb,ds32-100-fff

naxx:201-100-aaa,lkj1-250-ggg

baxx:ddd11-200-fff

suxx:ds32-100-ggg



As you see, I only need the Customer one time followed by part, amount and address - separated hyphen - comma, next part, amount, address and so on ...

I've tried the PIVOT way and STRAGG (wm_concat) but all failed so far.

The main problem is that when one customer fits more than one condition, ora throws: ORA-01427: single-row subquery returns more than one row

Any ideas ?
  • 1. Re: concat multiple output lines
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Welcome to the forum!
    user3409736 wrote:
    Hi all,

    I'm struggling with a problem for quite a while now and can't find any solution online so far ...

    OK I got three different Tables ...
    Whenever you have a problem, post CREATE TABLE and INSERT statements for your sample data.
    Since this is your first post, I'll do it for you:
    CREATE TABLE     cust_tbl
    (       cust_name     VARCHAR2 (10)
    ,     cust_id          NUMBER (6)     PRIMARY KEY
    );
    
    INSERT INTO cust_tbl (cust_name, cust_id) VALUES ('nuxx',  1);
    INSERT INTO cust_tbl (cust_name, cust_id) VALUES ('naxx',  2);
    INSERT INTO cust_tbl (cust_name, cust_id) VALUES ('baxx',  3);
    INSERT INTO cust_tbl (cust_name, cust_id) VALUES ('suxx',  4);
    
    
    CREATE TABLE      parts_tbl
    (       part_nr          VARCHAR2 (10)
    ,     amount          NUMBER
    ,     part_id          NUMBER (6)     PRIMARY KEY
    );
    
    INSERT INTO parts_tbl (part_nr, amount, part_id) VALUES ('x201',  100, 11);
    INSERT INTO parts_tbl (part_nr, amount, part_id) VALUES ('ds32',  100, 12);
    INSERT INTO parts_tbl (part_nr, amount, part_id) VALUES ('ddd11', 200, 13);
    INSERT INTO parts_tbl (part_nr, amount, part_id) VALUES ('lkj1',  250, 14);
    
    
    CREATE TABLE      order_tbl
    (       cust_id      NUMBER (6)
    ,     part_id      NUMBER (6)
    ,     address      VARCHAR2 (10)
    );
    
    INSERT INTO order_tbl (cust_id, part_id, address) VALUES (1, 11, 'aaa');
    INSERT INTO order_tbl (cust_id, part_id, address) VALUES (1, 11, 'bbb');
    INSERT INTO order_tbl (cust_id, part_id, address) VALUES (1, 12, 'ccc');
    INSERT INTO order_tbl (cust_id, part_id, address) VALUES (2, 11, 'ddd');
    INSERT INTO order_tbl (cust_id, part_id, address) VALUES (2, 14, 'eee');
    INSERT INTO order_tbl (cust_id, part_id, address) VALUES (3, 13, 'fff');
    INSERT INTO order_tbl (cust_id, part_id, address) VALUES (4, 12, 'ggg');
    COMMIT;
    See the forum FAQ {message:id=9360002}
    ... Now, what I need is an output like this:

    Customer, part1, amount1, address1,part2, amount2, addess2, ...

    e.g.

    nuxx:x201-100-aaa,x201-100-bbb,ds32-100-fff

    naxx:201-100-aaa,lkj1-250-ggg

    baxx:ddd11-200-fff

    suxx:ds32-100-ggg
    If you want separate columns for part1, amount1, address1, part2, ... then Pivot is what you want.
    If you want a big VARCHAR2 column, formatted to look like separate columns, then it's String Aggregation (such as STRAGG)
    Either way, see the forum FAQ {message:id=9360005}
    I've tried the PIVOT way and STRAGG (wm_concat) but all failed so far.
    What exactly have you tried? It's hard to say what you're doing wrong when we don't know what you;'re doing. Post your best attempt.
    The main problem is that when one customer fits more than one condition, ora throws: ORA-01427: single-row subquery returns more than one row
    That error is caused when you use a scalar sub-query.
    For either pivoting or string aggregation you don't need any scalar sub-queries.
  • 2. Re: concat multiple output lines
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Assuming you want string aggregation (which is best if you don't know how many different orders a customer will have), and assuming you have STRAGG installed, you can do something like this:
    SELECT    c.cust_name
    ,       STRAGG (  LPAD (p.part_nr, 8)
                 || TO_CHAR (p.amount, '9999')
               || ' '
               || RPAD (o.address, 6)
                 )        AS txt
    FROM       cust_tbl   c
    JOIN       order_tbl  o  ON  o.cust_id     = c.cust_id
    JOIN       parts_tbl  p     ON  p.part_id     = o.part_id
    GROUP BY  c.cust_name
    ORDER BY  c.cust_name
    ;
    Output:
    CUST_NAME  TXT
    ---------- -----------------------------------------------------------------
    baxx          ddd11  200 fff
    naxx           x201  100 ddd   ,    lkj1  250 eee
    nuxx           x201  100 bbb   ,    x201  100 aaa   ,    ds32  100 ccc
    suxx           ds32  100 ggg
  • 3. Re: concat multiple output lines
    xtender Pro
    Currently Being Moderated
    Try this one:
    with 
     cust_tbl(customer,id) as (
       select 'nuxx',1 from dual union all
       select 'naxx',2 from dual union all
       select 'baxx',3 from dual union all
       select 'suxx',4 from dual
    )
    ,parts_tbl(part_nr,amount,id) as (
       select 'x201',  100, 11 from dual union all
       select 'ds32',  100, 12 from dual union all
       select 'ddd11', 200, 13 from dual union all
       select 'lkj1',  250, 14 from dual
    )
    ,order_tbl(cust_id, part_id, address) as (
       select 1, 11, 'aaa' from dual union all
       select 1, 11, 'bbb' from dual union all
       select 1, 12, 'ccc' from dual union all
       select 2, 11, 'ddd' from dual union all
       select 2, 14, 'eee' from dual union all
       select 3, 13, 'fff' from dual union all
       select 4, 12, 'ggg' from dual
    )
    select 
        cust_tbl.customer
      , listagg(
                 parts_tbl.part_nr
                 ||'-'|| 
                 parts_tbl.amount
                 ||'-'||
                 order_tbl.address
               , ', '
        ) within group(order by parts_tbl.part_nr)
        aggregates
    from 
       order_tbl
       join cust_tbl 
          on order_tbl.cust_id = cust_tbl.id
       join parts_tbl
          on order_tbl.part_id = parts_tbl.id
    group by cust_tbl.customer
    Regards,
    Sayan Malakshinov
    http://orasql.org
  • 4. Re: concat multiple output lines
    975565 Newbie
    Currently Being Moderated
    Hi

    first of all, I'm actually not allowed to post the "real code" here, so I used an example to explain the problem.
    However FAQ4 does NOT apply here as we're talking about: how can I merge several rows into one big.

    So for each customer I need the "ordered part, amount and address" - which may occur more than just once. But must still appear in one line
    Sorry about the strange "names/dresses" but this forum prohibits the usage of reasonable names and/or cities ...


    EDIT: Uh, you guys where faster posting solutions as I could reply ... Thx Frank Kulash I'll gonna try that (tomorrow ;-) )

    Edited by: user3409736 on 21.11.2012 14:54
  • 5. Re: concat multiple output lines
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    user3409736 wrote:
    Hi

    first of all, I'm actually not allowed to post the "real code" here, so I used an example to explain the problem.
    Of course you can't post anything private. So make up some test data that shows what the problem is without using your real data, and perhaps without using your real table- abnd column names. Nobody besides you needs to know what the real names or values are. Post CREATE TABLE and INSERT statements for the disguised data, and the results you want from that data.
    However FAQ4 does NOT apply here as we're talking about: how can I merge several rows into one big.
    I don't understand. Did you read the FAQ? Pivoting and string aggregation are always about combining several small rows into 1 big row.
    So for each customer I need the "ordered part, amount and address" - which may occur more than just once. But must still appear in one line
    Sorry about the strange "names/dresses" but this forum prohibits the usage of reasonable names and/or cities ...
    Actually, the forum doesn't prohibit the use of reasonable names, but your business may prohibit the use of your real names.
    Anyway, that's no problem. The disguised values you chose are fine.
  • 6. Re: concat multiple output lines
    975565 Newbie
    Currently Being Moderated
    @Frank: I know this is quite OT but regarding the reasonable names -> [forum link|https://forums.oracle.com/forums/thread.jspa?threadID=2386516]
    I won't start arguing about FAQ 4 as it'll lead to nothing productive ... anyway you understood my problem and I'll try your proposal. Thx again :-)

    Edited by: user3409736 on 21.11.2012 17:04
  • 7. Re: concat multiple output lines
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    user3409736 wrote:

    So for each customer I need the "ordered part, amount and address" - which may occur more than just once. But must still appear in one line
    There are two basic issues.

    A SQL string is limited to 4000 bytes. Sooner or later, this concatenation approach is likely to exceed 4000 bytes of characters - and this approach is then broken. Then what?

    SQL should not be used to address client report rendering issues. SQL should deliver the string values as separate strings. The client should address the rendering part and, if required, do the concatenation of these string (and apply pretty fonts and shiny colours). This is a basic client-server concept.

    In my view, the vast majority of string concatenation (and aggregation) done in SQL, violates basic client-server concepts by addressing client rendering issues in the SQL projection, and violates designing and writing robust code (4000 byte char limit is kind of pathetic when concatenating strings).
  • 8. Re: concat multiple output lines
    user13179060 Newbie
    Currently Being Moderated
    SELECT cust_name||':'||wm_concat (part_id || '-' || amount || '-' || address) details
    FROM (SELECT o.cust_id,c.cust_name, p.part_id, p.amount, o.address
    FROM parts_tbl p, order_tbl o, cust_tbl c
    WHERE c.cust_id=o.cust_id
    and p.part_id = o.part_id)
    GROUP BY cust_id,cust_name
  • 9. Re: concat multiple output lines
    BluShadow Guru Moderator
    Currently Being Moderated
    user13179060 wrote:
    SELECT cust_name||':'||wm_concat (part_id || '-' || amount || '-' || address) details
    FROM (SELECT o.cust_id,c.cust_name, p.part_id, p.amount, o.address
    FROM parts_tbl p, order_tbl o, cust_tbl c
    WHERE c.cust_id=o.cust_id
    and p.part_id = o.part_id)
    GROUP BY cust_id,cust_name
    WM_CONCAT is an undocumented function and should NOT be used in any production system as it will render your code unsupported by Oracle.

    DO NOT recommend the use of undocumented functions, as solutions to issues.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points