String Aggregation In Oracle 10g With Three Columns
This is a sample table data Date | Fruit | Number ----------------------- 1 | Apple | 1 1 | Apple | 2 1 | Apple | 3 1 | Kiwi | 6 1 | Kiwi | 1
Solution 1:
For Oracle 10, using your approach - the issue is the partitioning in your inner query.
WITH tab as (
SELECT1as fdate, 'Apple' as fruit, 1 as num from dual unionSELECT1as fdate, 'Apple' as fruit, 2 as num from dual unionSELECT1as fdate, 'Apple' as fruit, 3 as num from dual unionSELECT1as fdate, 'Kiwi' as fruit, 6 as num from dual unionSELECT1as fdate, 'Kiwi' as fruit, 10 as num from dual unionSELECT2as fdate, 'Apple' as fruit, 4 as num from dual unionSELECT2as fdate, 'Apple' as fruit, 5 as num from dual unionSELECT2as fdate, 'Apple' as fruit, 6 as num from dual unionSELECT2as fdate, 'Kiwi' as fruit, 4 as num from dual unionSELECT2as fdate, 'Kiwi' as fruit, 7 as num from dual )SELECT fdate, fruit,LTRIM(MAX(SYS_CONNECT_BY_PATH(num,','))
KEEP (DENSE_RANK LAST ORDERBY curr),',') AS fruits_aggFROM (SELECT fdate,
fruit,
num,
ROW_NUMBER() OVER (PARTITION BY fdate, fruit ORDERBY num) AS curr,
ROW_NUMBER() OVER (PARTITION BY fdate, fruit ORDERBY num) -1AS prev
FROM tab)
GROUPBY 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 (
SELECT1as fdate, 'Apple' as fruit, 1 as num from dual unionSELECT1as fdate, 'Apple' as fruit, 2 as num from dual unionSELECT1as fdate, 'Apple' as fruit, 3 as num from dual unionSELECT1as fdate, 'Kiwi' as fruit, 6 as num from dual unionSELECT1as fdate, 'Kiwi' as fruit, 10 as num from dual unionSELECT2as fdate, 'Apple' as fruit, 4 as num from dual unionSELECT2as fdate, 'Apple' as fruit, 5 as num from dual unionSELECT2as fdate, 'Apple' as fruit, 6 as num from dual unionSELECT2as fdate, 'Kiwi' as fruit, 4 as num from dual unionSELECT2as fdate, 'Kiwi' as fruit, 7 as num from dual )select fdate
, fruit
, listagg(num,'-') within group ( order by num ) fruit_aggfrom tab
groupby 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
Post a Comment for "String Aggregation In Oracle 10g With Three Columns"