How to Merge Multiple Rows into Single in Oracle

How can multiple rows be concatenated into one in Oracle without creating a stored procedure?

There are many way to do the string aggregation, but the easiest is a user defined function. Try this for a way that does not require a function. As a note, there is no simple way without the function.

This is the shortest route without a custom function: (it uses the ROW_NUMBER() and SYS_CONNECT_BY_PATH functions )

SELECT questionid,
       LTRIM(MAX(SYS_CONNECT_BY_PATH(elementid,','))
       KEEP (DENSE_RANK LAST ORDER BY curr),',') AS elements
FROM   (SELECT questionid,
               elementid,
               ROW_NUMBER() OVER (PARTITION BY questionid ORDER BY elementid) AS curr,
               ROW_NUMBER() OVER (PARTITION BY questionid ORDER BY elementid) -1 AS prev
        FROM   emp)
GROUP BY questionid
CONNECT BY prev = PRIOR curr AND questionid = PRIOR questionid
START WITH curr = 1;

How to merge multiple rows into single in Oracle

Use hierachical query:

CREATE TABLE abcd(
  id int,
   A  varchar2(10),
   B varchar2(10),  
   C varchar2(10),  D varchar2(10)
);

insert all
  into abcd values(1,'x','y',null,'z')
  into abcd values(2,null,'z',null,'x')
  into abcd values(3,'p','w','a',null)
  into abcd values(4,'o',null,'k',null)
select 1 from dual;

commit;

WITH qq(id, a,b,c,d) AS(
   SELECT id, a,b,c,d FROM abcd WHERE id = 1
   UNION ALL
   SELECT x.id, 
          coalesce( x.a, qq.a ),
          coalesce( x.b, qq.b ),
          coalesce( x.c, qq.c ),
          coalesce( x.d, qq.d )
   FROM qq
   JOIN abcd x ON x.id = qq.id + 1
)
SELECT * FROM qq;

       ID A          B          C          D         
---------- ---------- ---------- ---------- ----------
         1 x          y                     z         
         2 x          z                     x         
         3 p          w          a          x         
         4 o          w          k          x

Oracle SQL - Multiple rows into one field

Use listagg after getting the distinct delivery types per product id. (Note that there is a 4000 character limit for the aggregated string.)

select product_id,listagg(delivery_type,'/') within group (order by delivery_type)
from (select distinct product_id,delivery_type from tbl) t
group by product_id

combine multiple rows into a single row in Oracle?

The function that you need is listagg() with does string concatenation when aggregating. You also need to first concatenate the ids together:

select id,
       listagg(m_id || ',' || s_m_id, ';') within group (order by m_id) as merge_ids
from test t
group by id;

By the way, the result data is incorrect (because the id is the same on all three rows). This probably accounts for the downvote.

EDIT (in response to comment):

You have two separators in the original example, one is comma (between ids) and one is a semicolon (between rows). You can replace either with '|' to get a pipe separator.

combine multiple rows result in single row based on one column value

Something like this usually helps; lines #1 - 7 represent your sample data. Code you need begins at line #8.

SQL> with test (seqnum, type, name) as
  2    (select 456, 'SH', 'Google2'      from dual union all
  3     select 456, 'CN', 'transwork'    from dual union all
  4     select 123, 'SH', 'partyshipper' from dual union all
  5     select 123, 'CN', 'consignee'    from dual union all
  6     select 999, 'XX', 'littlefoot'   from dual
  7    )
  8  select seqnum,
  9    max(case when type = 'CN' then name end) consigneename,
 10    max(case when type = 'SH' then name end) shipppername,
 11    max(case when type not in ('CN', 'SH') then name end) otherparty
 12  from test
 13  group by seqnum;

    SEQNUM CONSIGNEENAM SHIPPPERNAME OTHERPARTY
---------- ------------ ------------ ------------
       123 consignee    partyshipper
       999                           littlefoot
       456 transwork    Google2

SQL>

how combine multiple rows into a single row in Oracle?

Check the below query. Mostly it should work in your case. It may not execute at once, modify as per your requirement.


SELECT id,
         LISTAGG (unit || ' ' || unit_description, ', ')
            WITHIN GROUP (ORDER BY unit, unit_description)
    FROM (  SELECT id, unit_description, SUM (unit) AS unit
              FROM table1
          GROUP BY id, unit_description)
GROUP BY id;