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