Concatenate Results from a SQL Query in Oracle

Concatenate results from a SQL query in Oracle

-- Oracle 10g --

SELECT deptno, WM_CONCAT(ename) AS employees
  FROM   scott.emp
GROUP BY deptno;

Output:
     10  CLARK,MILLER,KING
     20  SMITH,FORD,ADAMS,SCOTT,JONES
     30  ALLEN,JAMES,TURNER,BLAKE,MARTIN,WARD

how to concatenate query results in the same column in oracle?

Please try below query, which works in ORACLE 11G:

select 
  listagg(email, ',') 
    within group (order by id) as list 
from operators
where op_code=1

SQL Fiddle Demo

OR

SELECT 
  (RTRIM(XMLAGG(xmlelement(X, EMAIL||',')order by id).extract('//text()'),',')) list
FROM operators
WHERE op_code=1

Concatenating multiple results of a query in one row in Oracle

You can use list_agg(). In your case:

select c.firstname, c.lastname,
       list_agg(p.product||'-'||p.desc, ' , ') within group (order by p.id) as product_and_desc
from customer c join
     product p
     on c.id = p.customer_id
group by c.firstname, c.lastname;

I would suggest, though, that the second argument to list_agg() be ', ' rather than ' , '. The space before the comma looks a bit unusual.

Concatenate values from SELECT with delimiter in ORACLE

You can use the LISTAGG function in Oracle.

select 'http://localhost/app/feeds/send?type=myType' || CHR(38) || 'ids=' ||

  LISTAGG(product_id,',') WITHIN GROUP (ORDER BY product_id)

from products where isin like 'AC%' and status in
('Active', 'Created', 'Live')
and
((pdate>to_date('07.05.2021','dd.MM.yyyy') or pdate is null));

DB Fiddle

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.

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;

Add SPACE to CONCAT with SUBSTR Oracle SQL

Use Concatenation Operator:

SELECT SUBSTR(FIRST_NAME,1,1)|| ' '||LAST_NAME AS NAME FROM OEHR_EMPLOYEES;

Or nested concat function:

SELECT concat(CONCAT(SUBSTR(FIRST_NAME,1,1), ' '),LAST_NAME) AS NAME FROM OEHR_EMPLOYEES;

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 query to concat comma separated column values

CONCAT doesn't behave nicely in this context as you have to nest several functions. But, the good, old double pipe concatenation operator || works well:

SQL> select ename ||','|| sal ||','|| job result
  2  from emp
  3  where rownum < 5;

RESULT
---------------------------------------------------
SMITH,1000,CLERK
ALLEN,1600,SALESMAN
WARD,1250,SALESMAN
JONES,2975,MANAGER

SQL>

---

Related Topics