9 Replies Latest reply: Nov 26, 2012 2:19 AM by BluShadow RSS

    concat multiple output lines

    975565
      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
          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
            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
              Sayan Malakshinov.
              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
                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
                  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
                    @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
                      Billy~Verreynne
                      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
                        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
                          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.