This content has been marked as final. Show 4 replies
Will there be times when you have three or more rows that you need to combine?
If so, you need string aggregation. See [this asktom page|http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2196162600402] for a couple of different ideas.
If not, you can do a self-join. Make it an outer join if you want to include rows that don't have a matching row with the same id.
Either way, if you don't have another column in the table, then you're as likely to get 'the opponents end zone When a individual croses' as the results you want.
user652714 wrote:Wouldn't that be nice, but no the SUM aggregate function doesn't operate on string data types. If you want to do something like SUM, you will need to create your own custom aggregate function. See the AskTom article cited earlier.
is there another way of doing what i explained above...with something like a sum(to_char... ?
Edited by: user652714 on Nov 25, 2008 10:44 AM
Do you have any column in your data that indicates the order in which your info column should be aggregated? if not you may get your phrases out of sequence and possibly generate a whole bunch of nonsense.
If you're using Oracle 10 (or higher) you may have an undocumented function wmsys.wm_concat, which does the same thiing as the user-defined fucntion stragg, which you can copy from the asktom site.
I don't recommend using undocumented functions, like wm_concat, for any Production applications, but you might use it just to decide if you want to install stragg or not. However, even if you don't use stragg for this job, you will want it for something else because it's so useful, so I suggest you copy and install it now.