Vertical pivoting in Oracle

Hi,

I am trying to do a vertical pivoting in Oracle using the LISTAAG functions.

The Query I am using is

select MEMBERS,GROUPBP,rownum as Set_Id,length(MEMBERS) as MEMBERS_LENGTH
from
(
select MEMBERS,
LISTAGG ( objc_sap_bp_id,'|') WITHIN GROUP
(ORDER BY objc_sap_bp_id) GROUPBP
from
(select objc_sap_bp_id,
LISTAGG ( SUBJ_SAP_BP_ID,'|') WITHIN GROUP
(ORDER BY SUBJ_SAP_BP_ID) MEMBERS
from sap_bp_relationship
where bp_rel_catg_code='XXXX' and objc_sap_bp_id not in ('XXX','YY')
and valid_to_date = to_date('99991231','YYYYMMDD')
group by objc_sap_bp_id )
group by MEMBERS)
WHERE length(GROUPBP) > 10

It works if the MEMBERS list is less than the maximum column size that Oracle can accomodate but if it grows than the required limit , it crashes because the length of the column is too big for the Oracle to accomodate.

Is there any way that I can avoid the Query to crash if the limit exceeds more than what is required.

I simply want to avoid those one's for which the member list is too big.

I tried substring but it did not help.
r4ramkiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Try the XML trick for string aggregation or use the stragg functions.

This previous question here has both examples:
http://www.experts-exchange.com/Database/Oracle/Q_24120361.html

The XML trick will return a CLOB.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.