Is this possible in oracle via a simple query, well i have a query that return multiple values as its output but is it possible to make the output as a single row with all the values concatenated with | symbol. is it possible without writing a oracle function.
the best way is to use LISTAGG aggregation function (available from 11gR2).
If you are not in 11gR2, use some technique described in this link: http://www.oracle-base.com/articles/...Techniques.php except WM_CONCAT, which separates values with comma (and is unsupported by Oracle anyway).
this is what i am looking for wm_concat(fieldname) this is a inbuilt function in oracle.
To be precise, this function is internal (= unsupported) in WMSYS schema (so not available when Oracle installed without it). Additionally, it separates the values with commas (as I stated before). You may REPLACE them with pipes, but the result will be incorrect when column values will contain commas. Use, whatever you want, in case of difficulties keep in mind that you were warned.
Some other discussion about WM_CONCAT: http://asktom.oracle.com/pls/apex/f?...23200346634568