Aggregate String Concatenation in Oracle 10G

string aggregation in Oracle 10g

You could try the collect function:

http://www.oracle-developer.net/display.php?id=306

Some other tricks are here:

http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php

...If you actually mean concatenation instead of aggregation then take everyone else's advice and use the || operator between the two strings:

select 'abc'||'def' from dual;

How to aggregate and string concatenate at same time in Oracle?

Would this do?

SQL> with test (customer, product, amount) as
  2    (select 'a', 'table', 500 from dual union all
  3     select 'a', 'table', 300 from dual union all
  4     select 'a', 'chair', 100 from dual union all
  5     select 'b', 'rug'  , 50  from dual union all
  6     select 'b', 'chair', 200 from dual
  7    )
  8  select customer,
  9    listagg (product, ', ') within group (order by null) product,
 10    sum(sum_amount) amount
 11  from (select customer, product, sum(amount) sum_amount
 12        from test
 13        group by customer, product
 14       )
 15  group by customer
 16  order by customer;

C PRODUCT                  AMOUNT
- -------------------- ----------
a chair, table                900
b chair, rug                  250

SQL>

String Aggregation in Oracle: Multiple Rows into Single Column

You have to escape the single-quotes

SELECT STRING_AGGREGATE('select ename||'' Job is ''||Job from emp') ENAMES FROM DUAL;

You can try out out how you pass the string to the function like this

SELECT 'select ename||'' Job is ''||Job from emp' FROM DUAL;

which gives you

select ename||' Job is '||Job from emp

See the demo: http://sqlfiddle.com/#!2/d41d8/23283

(By the way. There is a new feature LISTAGG since Oracle 11g which you may also want to have a look at.)

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

Ordering the strings while concatenating in oracle

to get an ordered list, there's a few ways. the simplest is :

select id, str
  from (select id, 
               wm_concat('level : ' || to_char(nvl(eventDate,SYSDATE - 365 * 100))) 
                 over (partition by id order by eventdate) str,
               row_number() over (partition by id order by eventdate desc) rn
         from Mytable)
 where rn = 1;

or if you're using the "stragg" user defined aggregate:

  select id, str
  from (select id, 
               string_agg('level : ' || to_char(nvl(eventDate,SYSDATE - 365 * 100))) 
                 over (partition by id order by eventdate) str,
               row_number() over (partition by id order by eventdate desc) rn
         from Mytable)
 where rn = 1;

eg

SQL> select id, str
  2    from (select id,
  3                 string_agg('level : ' || to_char(nvl(eventDate,SYSDATE - 365 * 100)))
  4                   over (partition by id order by eventdate) str,
  5                 row_number() over (partition by id order by eventdate desc) rn
  6           from Mytable)
  7   where rn = 1;

        ID STR
---------- ----------------------------------------------------------------------
         1 level : 27-MAR-08,level : 27-JAN-09,level : 02-APR-10
         2 level : 07-JUN-06,level : 02-NOV-08,level : 27-DEC-08
         3 level : 27-MAR-06,level : 02-APR-10,level : 27-JUL-10

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

---

Related Topics