Experts Exchange connects you with the people and services you need so you can get back to work.

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

Mosquitoe

Asked: 2011-09-28

Advertise Here

Oracle Database

1 Solution

LVL 79

slightwv (䄆 Netminder) Commented: 2011-09-28

Try this:

http://www.experts-exchange.com/Database/Oracle/Q_24914739.html?#a25864822

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

MosquitoeAuthor Commented: 2011-09-28

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';

MosquitoeAuthor Commented: 2011-09-28

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

LVL 79

slightwv (䄆 Netminder) Commented: 2011-09-28

I keep forgetting about listagg.

Try this:
...
LISTAGG(dwp.packaging_types, '||') WITHIN GROUP (order by packaging_types) AS PackagingList
...

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Oracle 11i pl/sql

Join & Write a Comment Already a member? Login.

Featured Post