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
