Fast Way to Generate Concatenated Strings in Oracle

Fast way to generate concatenated strings in Oracle

Tom Kyte provides a very convenient way to do that, and it works from Oracle 9i, with a custom aggregation function. It aggregates with commas, but you can modify the function body for pipes.

Starting with Oracle 11g, you can do:

SELECT LISTAGG(column, separator) WITHIN GROUP (ORDER BY field)
  FROM dataSource
 GROUP BY grouping columns

This web page provides additional methods including the one that you listed and which is indeed not really efficient.

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.

What is the string concatenation operator in Oracle?

It is ||, for example:

select 'Mr ' || ename from emp;

The only "interesting" feature I can think of is that 'x' || null returns 'x', not null as you might perhaps expect.

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 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

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 - Is there a better way to concatenate multiple strings with a given delimiter?

We can still write our own AWESOMECONCAT(). Unfortunately, Oracle has no in built function. As the concatenate operator does the basic thing.

Using double quotes in the alias, you can make the column references case sensitive and even accept blanks. But note that, any more references to that column/expression needs double quotes with same text.

SELECT member_no as "Member #",
(member_gname
|| ' '
|| member_fname) as Name,
(member_street
|| ' '
|| member_city
|| ' '
|| member_state
|| ' '
|| member_postcode) AS Address,
member_phone AS Phone,
TO_CHAR(member_joindate, 'dd-Mon-yyyy') as "Join Date"
FROM MEMBER;

sql: Aggregate / gather strings within one column (with a single query)

You need to use wm_concat(fieldname) to solve the purpose.
So your query will be :

SELECT attr1, wm_concat(attr2) FROM YourTable GROUP BY field2;

And If you want Duplicates to be removed then

SELECT attr1, wm_concat(distinct attr2) FROM YourTable GROUP BY field2;

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

---

Related Topics