Concatenate rows, based on common column Oracle

Question

I have to concatenate below data

a b c  
1 Text 22-03-2015
1 Text1 22-03-2015
2 Text2 24-05-2015
3 Text5 29-05-2015
1 Text11 23-03-2015

And the expected Output is

a b c
1 Text,text1 22-03-2015
1 Text11 23-03-2016
2 Text2 24-05-2015
3 Text5 29-05-2015

Tried wm_concat function and it works but i cant remove the duplicates from the data and usage of LISTAGG results in error

"ora-01489 result of string concatenation is too long in oracle"

error as columns b contains values greater than 4000 characters.
Any alternatives apart form these 2 functions?

One option would be to use a subquery with `distinct`. This might help: http://stackoverflow.com/questions/19577257/wm-concat-with-distinct-clause-compiled-package-versus-stand-alone-query-issue — sgeddes, Oct 03 '16 at 01:21
It's not clear why from your sample data. You should provide sample data that illustrates the problem you are having. Perhaps create a sqlfiddle.com to demonstrate. — sgeddes, Oct 03 '16 at 01:43
SELECT a ,to_char(c,'DD.MM.YYYY') enddate ,type ,wm_concat( type || ': (' || to_char (c,'HH:MI:SS AM') || ') ' || b ) output FROM table GROUP BY a ,to_char(c,'DD.MM.YYYY') ,type — Annie Jeba, Oct 03 '16 at 02:38

score 1 · Accepted Answer · answered Oct 03 '16 at 03:04

1

You can use LISTAGG function if you are Oracle 11g+

SELECT a, LISTAGG(b, ' ') WITHIN GROUP (ORDER BY b) as b, c
FROM   yourtable
GROUP BY a,c;

answered Oct 03 '16 at 03:04

Pரதீப்

Does LISTAGG Function has limit of 4000 characters in Oracle 12c? – Annie Jeba Oct 03 '16 at 03:09
1

@AnnieJeba Yes when string is longer than 4000 bytes it will limit the result. Check this answer for more info http://stackoverflow.com/questions/13795220/listagg-function-result-of-string-concatenation-is-too-long – Pரதீப் Oct 03 '16 at 03:13

1 Answers1