Converting Oracle tables' rows to columns by using LISTAGG

hi All,

Not once I been challenged with this particular issue: how to display multiple rows data in a single column?
There are numerous resources for doing so by using XMLAGG which is fair if you go straight to XML output.
In my case, XML wasn't the goal and I rather prefer to have simple fetch in PL/SQL developer.Good thing on 11g we have WITHIN GROUP and LISTAGG to have it the way I need.
Following post provides detailed example of the feature described at http://www.dba-oracle.com/t_converting_rows_columns.htm

Problem: On the Batch Header there are multiple text paragraphs defined to store varied information:

In our case its a text paragraph called Stability Study Requirements used by Quality Personnel. Every text paragraph (the text stored within) associated with his Batch Header by text_code.
(gme_batch_header.text_code = gme_text_table.text_code):

The problem occurs when same text_code has multiple text lines (long text inserted into the window above) cause single line has only 70 character length, so while trying to fetch the whole Paragraph text we might have several lines retrieved...

Solution: By using within group SQL clause we could pivot multiple Paragraphs' text rows onto a single row and then using the listagg() built-in function to display multiple column values in a single column.


Example: Displaying all Finish Goods' batches that have Stability Study Requirement text paragraph allocated. Whole paragraphs' text displayed as a single column:



select t.batch_no "Batch No",
       t.item_no  "Item Number",
       t.plant_code  "Plant Code",
       text.paragraph_text "Stability Text Paragraph"
from (select    iim.item_no,
                gbh.plant_code,
                gbh.batch_no,
                gbh.text_code
         from   gme_batch_header          gbh,
                gme_material_details      gmd,
                ic_item_mst_b             iim,
                ic_item_mst_b_dfv         im_dfv,
                ic_lots_mst               ilm
         where  1 = 1
           and  gbh.actual_start_date > to_date('01-JAN-2011', 'DD-MON-YYYY')        
           and  gmd.batch_id       = gbh.batch_id
           and  gmd.line_no        = 1
           and  gmd.line_type      = 1
           and  iim.item_id        = gmd.item_id
           and  iim.rowid          = im_dfv.row_id
           and  iim.inactive_ind   = 0
           and  im_dfv.item_type  in 10 --FG
           and  iim.item_id        = ilm.item_id 
           and  ilm.lot_no        <> fnd_profile.value('IC$DEFAULT_LOT')
           and  exists (select 1
                        from gme_text_table_tl  gtt
                        where  gtt.language      = userenv('LANG')
                        and  gtt.lang_code       = userenv('LANG')
                        and  gtt.paragraph_code = 'STAB'
                        and  gtt.text_code = gbh.text_code) 
        group by iim.item_no,
                 gbh.plant_code,
                 gbh.batch_no,
                 gbh.text_code)t
     ,(select gtt.text_code code
              ,listagg (gtt.text, '')
              within group
              (ORDER BY gtt.line_no) paragraph_text
              from gme_text_table_tl         gtt
              where 1 = 1
              and  gtt.language       = userenv('LANG')
              and  gtt.lang_code      = userenv('LANG')
              and  gtt.paragraph_code = 'STAB'
              and  gtt.text is not null
              GROUP BY 
              gtt.text_code)text
where text.code = t.text_code