First 7 bill and card no for Specific Store

select a.store,s.store_name,s.store_open_date ,min(RANK1_TRAN_NO) R1_TRAN_NO,min(RANK1_TRAN_NO_1)R1_CARD_NO , min(rank2_Tran_no) R2_TRAN_NO,min(rank2_Tran_no_1) R2_CARD_NO, min( rank3_Tran_no) R3_TRAN_NO, min( rank3_Tran_no_1) R3_CARD_NO, min( rank4_Tran_no) R4_TRAN_NO,min(rank4_Tran_no_1 ) R4_CARD_NO, min(rank5_Tran_no ) R5_TRAN_NO,min( rank5_Tran_no_1) R5_CARD_NO ,min(rank6_Tran_no ) R6_TRAN_NO,min( rank6_Tran_no_1) R6_CARD_NO ,min(rank7_Tran_no ) R7_TRAN_NO,min( rank7_Tran_no_1) R7_CARD_NO from ( select store, (case when ro = 1 then tran_no end)RANK1_TRAN_NO,(case when ro = 1 then card_no end) RANK1_TRAN_NO_1 , (case when ro = 2 then tran_no end)rank2_Tran_no,(case when ro = 2 then card_no end) rank2_Tran_no_1 ,(case when ro = 3 then tran_no end)rank3_Tran_no,(case when ro = 3 then card_no end) rank3_Tran_no_1 ,(case when ro = 4 then tran_no end)rank4_Tran_no,(case when ro = 4 then card_no end) rank4_Tran_no_1 ,(case when ro = 5 then tran_no end)rank5_Tran_no,(case when ro = 5 then card_no end) rank5_Tran_no_1 ,(case when ro = 6 then tran_no end)rank6_Tran_no,(case when ro = 6 then card_no end) rank6_Tran_no_1 ,(case when ro = 7 then tran_no end)rank7_Tran_no,(case when ro = 7 then card_no end) rank7_Tran_no_1 from ( select store,tran_no, tran_datetime, card_no,ro from ( select store,tran_no, tran_datetime, card_no,rank ro from ( select store,tran_no,tran_datetime, ref_no3 card_no, dense_rank() over ( partition by store order by tran_datetime ) rank from ( select * from rmsanpowner.abrl_sa_tran_head_anp where tran_type in('SALE','RETURN') and status = 'P' and store in ( 1628 ) and ref_no3 like '10%' union select * from rmsowner.sa_tran_head@rmsanp2rmsprod where tran_type in('SALE','RETURN') and status = 'P' and store in ( 1628 ) and ref_no3 like '10%' ) ) where rank <= 7 ) ) group by store, (case when ro = 1 then tran_no end),(case when ro = 1 then card_no end) ,(case when ro = 2 then tran_no end),(case when ro = 2 then card_no end) ,(case when ro = 3 then tran_no end),(case when ro = 3 then card_no end) ,(case when ro = 4 then tran_no end),(case when ro = 4 then card_no end) ,(case when ro = 5 then tran_no end),(case when ro = 5 then card_no end) ,(case when ro = 6 then tran_no end),(case when ro = 6 then card_no end) ,(case when ro = 7 then tran_no end),(case when ro = 7 then card_no end) )a,store@rmsanp2rmsprod s where a.store = s.store group by a.store ,s.store_name,s.store_open_date