How to aggregate string( concatenate) with Oracle 10g SQL?
2
-
2Aggregation and concatenation are not quite the same thing. Which did you mean?FrustratedWithFormsDesigner– FrustratedWithFormsDesigner2010-03-10 16:00:38 +00:00Commented Mar 10, 2010 at 16:00
-
what I ment was to aggregate where aggregation function is concatenation. let's say data table looks like this: class text A TEXT1 A TEXT2 B TEXT3 B TEXT4 I would like to have group by query select class, function(text) from TABLE group by class that results with something like: A TEXT1, TEXT2 B TEXT3, TEXT4acroa– acroa2010-03-16 14:15:45 +00:00Commented Mar 16, 2010 at 14:15
Add a comment
|
5 Answers
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;
answered Mar 10, 2010 at 15:59
FrustratedWithFormsDesigner
27.7k31 gold badges151 silver badges211 bronze badges
Comments
Oddly enough, it's the "||" operator:
field1 || field2
Comments
You could use the || operator. Ex: 'First' || 'Second'
Also the function CONCAT(var1, var2) allows you to concatenate two VARCHAR2 characters. Ex: CONCAT('First', 'Second')
Comments
There is an undocumented function wm_concat that you can use. Another option would be to roll your own. LISTAGG isn't available in 10g, I think.
