SELECT
T.t ransaction_id,
MAX (IF (rn)=1, t.p lan_amount_total, NULL)) AS first_plan_amount,
MAX (IF (rn)=1, t.f ully_paid, NULL)) AS first_is_fully_paid,
MAX (IF (rn)=2, t.p lan_amount_total, NULL)) AS second_plan_amount,
MAX (IF (rn)=2, t.p lan_receipt_date, NULL)) AS second_receipt_date,
MAX (IF (rn)=2, t.f ully_paid, NULL)) AS second_is_fully_paid,
MAX (IF (rn)=3, t.p lan_amount_total, NULL)) AS third_plan_amount,
MAX (IF (rn)=3, t.p lan_receipt_date, NULL)) AS third_receipt_date,
MAX (IF (rn)=3, t.f ully_paid, NULL)) AS third_is_fully_paid,
MAX (IF (rn)=4, t.p lan_amount_total, NULL)) AS four_plan_amount,
MAX (IF (rn)=4, t.p lan_receipt_date, NULL)) AS four_receipt_date,
MAX (IF (rn)=4, t.f ully_paid, NULL)) AS four_is_fully_paid,
MAX (IF (rn)=5, t.p lan_amount_total, NULL)) AS fifth_plan_amount,
MAX (IF (rn)=5, t.p lan_receipt_date, NULL)) AS fifth_receipt_date,
MAX (IF (rn)=5, t.f ully_paid, NULL)) AS fifth_is_fully_paid
The FROM
(SELECT
@ r: case when @ transactionId==p1. Transaction_id then @ r + 1 else 1 end as an rn,
P1. Transaction_id AS transactionId,
P1. *
The FROM
(SELECT
P.t ransaction_id,
P.p lan_amount_total,
P.p lan_receipt_date,
IF
(SUM (item. Actual_amount)=p.p lan_amount_total, 'is',' no ') AS fully_paid
The FROM
Yxcw. Fin_payment_plan p
INNER JOIN rep_param_project pp ON (pp. Job_id=p_report_log_id AND pp. Project_num=p.p roject_num)
LEFT the JOIN yxcw. Fin_actual_received_item item ON the item. Is_deleted=0
AND the item. Payment_plan_id=p.i d
WHERE
1=1
AND p.f und_name_code NOT IN (' FIFT01001 ', 'FIFT01004')
AND p.f und_type_code IN (' FIFT01 ', 'FIFT02)
GROUP BY
P.i d
The ORDER BY
P.p lan_receipt_date ASC,
P.i d ASC) p1,
: (select @ r=0, @ transactionId:=' ')
bThe ORDER BY
P1. Transaction_id,
P1. Plan_receipt_date
) t
GROUP BY
T.t ransaction_id

CodePudding user response:

IF (rn)=1, t.p lan_amount_total, NULL)
=="
Decode (rn), 1, t.p lan_amount_total)

@ r: case=
The when @ transactionId=p1. Transaction_id then
@ r + 1
The else
1
End as an rn
: (select @ r=0, @ transactionId:=' ')
=="
1 + sum (decode (p1) transaction_id, null, 1, 0)) over (order by p1. Transaction_id, p1 plan_receipt_date)

CodePudding user response:

reference 1st floor nayi_224 response:

IF (rn)=1, t.p lan_amount_total, NULL)
=="
Decode (rn), 1, t.p lan_amount_total)

@ r: case=
The when @ transactionId=p1. Transaction_id then
@ r + 1
The else
1
End as an rn
: (select @ r=0, @ transactionId:=' ')
=="
1 + sum (decode (p1) transaction_id, null, 1, 0)) over (order by p1. Transaction_id, p1 plan_receipt_date)

The second paragraph is a little problem,,,
Grammar to you, yourself litigated

 with tab1 as (
Select "id, one word from dual union all 
Select "id, 2 from dual union all 
Select 'id, 3 from dual union all 
Select '1121' id, 4 from dual union all 
Select 'id, 5 from dual union all 
Select 'id, 6 from dual union all 
Select '11122' id, 7 from dual union all 
Select 'id, 8 from dual 
) 
Tab2 as (
Select a t1. *, 
The sum (decode (t1. Id, null, 0, 1)) over (order by word group_) 
The from tab1 t1 
) 
Select a t1. *, 
Row_number () over (partition by t1. Group_ order by t1. The word) rn 
The from tab2 t1 
;