oracle 竖向改横向,oracle 纵向变横向

select type_amt,TICKET_NO from (

select rank() over(partition by TICKET_NO order by length(type_amt) desc) type_amt_no,type_amt,TICKET_NO

from (

SELECT TICKET_NO,

LTRIM(

replace( SYS_CONNECT_BY_PATH(TAX_TYPE||''||to_char(TAX_AMT,'FM9999999999999990D00'),','),'',','),

','

) type_amt

FROM (SELECT TICKET_NO,

TAX_TYPE,

TAX_AMT,

MIN(TAX_TYPE) OVER(PARTITION BY TICKET_NO) maxType_MIN,

(ROW_NUMBER() OVER(ORDER BY TICKET_NO,TAX_TYPE,TAX_AMT))+(DENSE_RANK() OVER (ORDER BY TICKET_NO)) NUMID

FROM t_atr_tax T

)

START WITH TAX_TYPE=maxType_MIN

CONNECT BY NUMID-1 = PRIOR NUMID

order by TICKET_NO, length(type_amt)

)

)

where type_amt_no = 1

----------------

select ticket_no, rtrim (xmlagg (xmlelement (e, TAX_TYPE|| ',' || TAX_AMT||',')).extract ('//text()'), ',') TAX_AMT

from T_ATR_TAX where ticket_no in (select ticket_no from t_atr_ticket)

group by ticket_no