String Concatenation

We're on Oracle 10g. I have a huge dynamic query that is built in a PL/SQL procedure. The query works and it dynamically puts together the criteria based on parameters passed into the procedure.

However, there is one table in which the values need to be returned in A:B,C:D,E:F,G:H

So, the query uses ROW_NUMBER() to get the first record in each set. But, for one of the joined tables I need to combine the rows into 1 column in that delimited format.

Right now I am using this as one of the columns in the query:

(SELECT RTRIM (XMLAGG (XMLELEMENT (e, t.tag_id||'':''||tag_name || '','')).EXTRACT (''//text()''),'','')

                       FROM tags t, article_tags at
                       WHERE a.article_id = at.article_id
                       AND at.tag_id = t.tag_id
                       AND t.tag_id IN (' || v_tag_id || ')) tag_list

Let me tell you, with that it slows the query down where it runs nearly 5 minutes. I'm hoping not to have to code yet another query, with the same dynamic criteria, just to get the list.

Does anyone have a good idea on how to do this aggregation? I'm looking to try anything right now. Received on Mon Jun 14 2010 - 16:33:40 CDT