Oracle String Aggregation

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

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>

Oracle String Aggregation using ROW_NUMBER()

Use a case expression to influence the ordering, here is an example:

SELECT
    ROW_NUMBER()  OVER (ORDER BY
                         case when A.CARMODEL IN('car1','car2') then 1 else 2 end
                       , A.CARMODEL)
    AS seq_num
  , 'A'    AS SALES_CATEGORY
  , '1000' AS SALES_TARGET
FROM x

or:

SELECT
    ROW_NUMBER()  OVER (ORDER BY
                         case when A.CARMODEL = 'car1' then 1
                                   A.CARMODEL = 'car2' then 2
                              else 3 end
                       , A.CARMODEL)
    AS seq_num
  , 'A'    AS SALES_CATEGORY
  , '1000' AS SALES_TARGET
FROM x

Oracle SQL: Alternative to aggregate large texts (when exceeding Listagg limit)

One of possible method.

select xmlagg(xmlelement(xxx,'ITEM NO.: ' || m.ITEM || 
        ' -nl-ARTICLE: ' || a.ARTICLE || 
        ' -nl-NET: ' || m.NET || 
        ' -nl-TAX: ' || NVL(m.TAX, 0) || 
        ' -nl-GROSS: ' || (m.NET + m.TAX),
        ' -nl--nl-'||',<-separator').extract('//text()') order m.S_ID).getClobval() from mytable
group by ...

2nd method.
oracle allows to creat own aggregation function user defined aggregation function

String Aggregation in ORACLE 10g with three columns

For Oracle 10, using your approach - the issue is the partitioning in your inner query.

WITH tab as (
SELECT 1 as fdate,     'Apple' as fruit,    1 as num from dual union
SELECT 1 as fdate,     'Apple' as fruit,    2 as num from dual union
SELECT 1 as fdate,     'Apple' as fruit,    3 as num from dual union
SELECT 1 as fdate,     'Kiwi' as fruit,    6 as num from dual union
SELECT 1 as fdate,     'Kiwi' as fruit,    10 as num from dual union
SELECT 2 as fdate,     'Apple' as fruit,    4 as num from dual union
SELECT 2 as fdate,     'Apple' as fruit,    5 as num from dual union
SELECT 2 as fdate,     'Apple' as fruit,    6 as num from dual union
SELECT 2 as fdate,     'Kiwi' as fruit,    4 as num from dual union
SELECT 2 as fdate,     'Kiwi' as fruit,    7 as num from dual )
SELECT fdate, fruit,LTRIM(MAX(SYS_CONNECT_BY_PATH(num,','))
    KEEP (DENSE_RANK LAST ORDER BY curr),',') AS fruits_agg
    FROM   (SELECT fdate,
            fruit,
            num,
            ROW_NUMBER() OVER (PARTITION BY fdate, fruit ORDER BY num) AS curr,
            ROW_NUMBER() OVER (PARTITION BY fdate, fruit ORDER BY num) -1 AS prev
     FROM   tab)
  GROUP BY fdate,fruit
  CONNECT BY prev = PRIOR curr AND fruit = PRIOR fruit AND fdate = PRIOR fdate
 START WITH curr = 1;

Gives:

FDATE             FRUIT   FRUITS_AGG                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
1                "Kiwi"  "6,10"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
1                "Apple" "1,2,3"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
2                "Kiwi"  "4,7"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
2                "Apple" "4,5,6"

The Oracle 11 solution is a whole lot easier:

WITH tab as (
SELECT 1 as fdate,     'Apple' as fruit,    1 as num from dual union
SELECT 1 as fdate,     'Apple' as fruit,    2 as num from dual union
SELECT 1 as fdate,     'Apple' as fruit,    3 as num from dual union
SELECT 1 as fdate,     'Kiwi' as fruit,    6 as num from dual union
SELECT 1 as fdate,     'Kiwi' as fruit,    10 as num from dual union
SELECT 2 as fdate,     'Apple' as fruit,    4 as num from dual union
SELECT 2 as fdate,     'Apple' as fruit,    5 as num from dual union
SELECT 2 as fdate,     'Apple' as fruit,    6 as num from dual union
SELECT 2 as fdate,     'Kiwi' as fruit,    4 as num from dual union
SELECT 2 as fdate,     'Kiwi' as fruit,    7 as num from dual )
select fdate
     , fruit
     , listagg(num,'-') within group ( order by num ) fruit_agg
from tab
group by fdate, fruit 

Returns:

FDATE  FRUIT    FRUIT_AGG
1      Kiwi      6-10
1      Apple     1-2-3
2      Kiwi      4-7
2      Apple     4-5-6

---

Related Topics