Wolfram Roesler wrote:
> Frank van Bortel <frank.van.bortel_at_gmail.com> wrote in news:e2kv13$5ej$1
> @news3.zwoll1.ov.home.nl:
>
>> Sum strings?!
>> SQL Server background, where concatenation is done with '+'?
>
> No :)
>
>> In Oracle it's '||' - two vertical bars
>
> Yes, thanks, but it won't work with GROUP BY, just like
> '+' doesn't for numbers. Look at my example: What should
> I write instead of SUM if colname was a VARCHAR2? As it
> seems, however, there's no easy solution.
Does your Oracle version support XML?
XMLSERIALIZE(XMLAGG(XMLELEMENT(.. 'the string'))).
As a result you get an XML Sequence as a string. Then all you need to do
is replace the XML tags with commas using a couple of REPLACE functions.
The trick is in XMLAGG() it's the only pre-defined aggregate function
that doesn't throw away it's input...
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Received on Wed Apr 26 2006 - 07:08:18 CDT
