String Aggregation in ORACLE 10g with three column

Question

This is a sample table data

  Date | Fruit | Number
 -----------------------
   1   | Apple |   1
   1   | Apple |   2
   1   | Apple |   3
   1   |  Kiwi |   6
   1   |  Kiwi |  10
   2   | Apple |   4
   2   | Apple |   5
   2   | Apple |   6
   2   |  Kiwi |   4
   2   |  Kiwi |   7

I try to concatenate the table column values to get the following:

  Date | Fruit | Number
 -----------------------
   1   | Apple |  1-2-3
   1   |  Kiwi |   6-10
   2   | Apple |  4-5-6
   2   |  Kiwi |    4-7

Code that I use:

SELECT fruit,
       LTRIM( MAX(SYS_CONNECT_BY_PATH(number,','))
               KEEP (DENSE_RANK LAST ORDER BY curr), ',') AS fruits_agg
  FROM( SELECT Date,
               fruit,
               number,
               ROW_NUMBER() OVER (PARTITION BY fruit ORDER BY number) AS curr,
               ROW_NUMBER() OVER (PARTITION BY fruit ORDER BY number) - 1 AS prev
          FROM table_name)

  GROUP BY Date,fruit
  CONNECT BY prev = PRIOR curr AND fruit = PRIOR fruit AND Date = PRIOR Date
  START WITH curr = 1;

It doesn't work the way I want it to be. Where Did I do wrong?

PS: I'm on version 10g, so I can't use listagg.

Answer 1

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