Concatenate and Group Multiple Rows in Oracle

SQL Query to concatenate column values from multiple rows in Oracle

There are a few ways depending on what version you have - see the oracle documentation on string aggregation techniques. A very common one is to use LISTAGG:

SELECT pid, LISTAGG(Desc, ' ') WITHIN GROUP (ORDER BY seq) AS description
FROM B GROUP BY pid;

Then join to A to pick out the pids you want.

Note: Out of the box, LISTAGG only works correctly with VARCHAR2 columns.

Concatenate across columns and rows using group and listagg in Oracle SQL

Use the following query where you will directly get concatenated column values:

SELECT
    "GROUP",
    LISTAGG(VAL_1 || VAL_2 || VAL_3)  
        WITHIN GROUP(ORDER BY VAL_1) AS "TEXT"
FROM DATA
GROUP BY "GROUP";

Note: Do not use oracle reserved keywords as the column names. Here GROUP is the oracle reserved keyword.

Cheers!!

Concatenate and group multiple rows in Oracle

Consider using LISTAGG function in case you're on 11g:

select grp, listagg(name,',') within group( order by name ) 
  from name_table group by grp

sqlFiddle

upd: In case you're not, consider using analytics:

select grp,
    ltrim(max(sys_connect_by_path
       (name, ',' )), ',')
        scbp
  from (select name, grp,
            row_number() over
           (partition by grp
            order by name) rn
         from tab
          )
start with rn = 1
connect by prior rn = rn-1
and prior grp = grp
  group by grp
  order by grp

sqlFiddle

Oracle - SQL to concatenate multiple rows

If you have Oracle 11g you could use the LISTAGG() function for this:

SELECT
      id
    , listagg(str) WITHIN GROUP (ORDER BY OFFSET) AS str_of_str
FROM yourtable
GROUP BY id

see: http://docs.oracle.com/cd/E11882_01/server.112/e10592/functions089.htm
and this sqlfiddle

  | ID |               STR_OF_STR |
    |----|--------------------------|
    |  1 | TestSTR1TestSTR3TestSTR5 |
    |  2 |         TestSTR4TestSTR2 |
    |  3 |                 TestSTR6 |

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;

concatenate column values from multiple rows in Oracle without duplicates

One option would be using regexp_replace():

select regexp_replace(
                      listagg( t.id,',') within group (order by t.id)
                      , '([^,]+)(,\1)+', '\1') as "Result"
  from t

Demo

Concatenate certain number of rows and split into different groups

You can use a subquery against your real table to put the client IDs into buckets, and then include the bucket in the grouping:

select location_id,
  RTRIM(XMLCAST(XMLAGG(XMLELEMENT(E,'"'||client_id||'",')) AS CLOB),',') AS clientid
from (
  select location_id,
    client_id,
    ceil(row_number() over (partition by location_id order by client_id) / 900) as bucket
  from tab
)
group by location_id, bucket

db<>fiddle (split after 9 instead of 900 to demonstrate the principal)

How to group and concatenate results from multiple records?

You are looking for listagg():

select a.id, listagg(b.value, ',') within group (order by b.value)
from a left join
     b
     on a.name = b.name
where a.id = 12345
group by a.id;

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

Concatenate multiple columns from multiple rows in Oracle

This with some modification from here . Added || (concatenate) in LISTAGG

SELECT ID,
   LISTAGG (
      CASE
      WHEN TIPE = 'v' THEN
         C_STRING
      ELSE
         TO_CHAR (C_NUMBER)
      END || OP,
      ' '
   ) WITHIN GROUP (ORDER BY URUT) AS CONCAT_RESULT
FROM KOMPONEN
GROUP BY ID;