title: Transpose rows to columns in Oracle 10g
body: <p>I have a table which looks like the following</p>
  
  <pre><code>po_num   | terms type  | terms description
  -------------------------------------------
  10       | 1           | Desc-10-1
  10       | 2           | Desc-10-2
  10       | 3           | Desc-10-3
  20       | 1           | Desc-20-1
  20       | 3           | Desc-20-3
  30       |             | 
  </code></pre>
  
  <p>So, for each Purchase Order (PO_NUM) there could be several terms of agreements (maximum three - 1,2,3) or no terms of agreement at all. Now, what I need is to transpose rows into columns - that is, for each po_num, I would like to have a similar output like below.</p>
  
  <pre><code>po_num  | terms1      | termsDesc2  | terms2     | termsDesc2  | terms3    |termsDesc3
  ---------------------------------------------------------------------------------------
  10       | 1           | Desc-10-1  | 2          | Desc-10-2   | 3         |Desc10-3
  20       | 1           | Desc-20-1  |            |             | 3         |Desc20-3
  30       |             |            |            |             |           |
  </code></pre>
  
  <p>I cannot use pivot since I don't have Oracle 11.2 installed. I do not wanna use scalar subqueries in the select because the performance degrades several times with that approach. I tried to use the following query to first concatenate all the fields, the split them with an outer query, but haven't been able to do it yet. </p>
  
  <pre><code>    SELECT po_num,
           RTRIM (
              XMLAGG (
                 XMLELEMENT (
                    po_table,
                    po_table.terms_id || '|' || po_table.terms_description || '|')).
              EXTRACT ('//text()'),
              '|')
              po_concat
      FROM po_table
     WHERE 1 = 1
     GROUP BY PO_table.po_num
  </code></pre>