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?
 
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
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.

All Courses

From novice to tech pro — start learning today.