4 Replies Latest reply: Nov 25, 2008 1:06 PM by Frank Kulash RSS

    Combine rows?

    655717
      I'm trying to combine two rows under one column.

      using: oracle 10g

      Here is my code
      select 
          cr.report_id as report_id,
          cr.report_name as report_name,
          ei.info as info
      from SPSS.WEB_CLIENT_REPORTS_NFL CR,  spss.web_report_extra_info_nfl EI
      where cr.report_id = ei.report_id
      current output
      report_id /  report_name    / info
      11111        player scored      When a individual croses     
      11111        player scored      the opponents end zone
      what i want the output to be
      report_id /  report_name    / info
      11111        player scored      When a individual croses the opponents end zone   
      Thanks
        • 1. String Aggregate
          Frank Kulash
          Hi,

          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.
          • 2. Re: String Aggregate
            655717
            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
            • 3. Re: String Aggregate
              Sentinel
              user652714 wrote:
              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
              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.

              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.
              • 4. Re: String Aggregate
                Frank Kulash
                Hi,

                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.