asked on

Oracle 11i pl/sql

Hello,

I need to have a result set that is pipe separated - Is this something that I can write just in a query or will I need to actually loop through the result set and create it.
For example results are:
1
2
3
I want only one return that looks like this: 1 || 2 || 3

slightwv (䄆 Netminder)

Try this:

https://www.experts-exchange.com/questions/24914739/In-Oracle-how-can-I-concatenate-multiple-results-returned-from-select-statement-into-a-comma-separated-string.html?&anchorAnswerId=25864822#a25864822

SELECT
RTRIM(
EXTRACT(XMLAGG(XMLELEMENT("s", yourtable.column3 || '||')), '/s/text()').getstringval(),
'||'
)
column3
FROM yourtable
;

Mosquitoe

ASKER

I keep getting an error on this (I had tried to use the listagg function as well - but same error) Keeps telling me I am missing left parenthesis but I must be blind because I cannots ee what i am missing:

SELECT
RTRIM(
EXTRACT(XMLAGG(XMLELEMENT("s", dossier_waste_packaging dwp.dwp.packaging_types || '||')), '/s/text()').getstringval(),
'||'
)
dwp.packaging_types
FROM dossier_waste_packaging dwp
WHERE dwp.dos_hwmd_sys_entered_yr = 10
AND dwp.dossier_identifier = 325
AND dwp.dossier_type_code = 'EXP';

Mosquitoe

ASKER

I tried this as well:

SELECT DISTINCT LISTAGG(dwp.packaging_types, '||') WITHIN GROUP AS PackagingList
FROM dossier_waste_packaging dwp
WHERE dwp.dos_hwmd_sys_entered_yr = 10
AND dwp.dossier_identifier = 325
AND dwp.dossier_type_code = 'EXP

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial