Hello Everyone, Would that be possible to calculate the number of Participant and lead for each loan by the loan ID for example this is my table

loan_ID Firms Lender_role
1 A Participants
1 B Lead
1 C Participants

2 B Lead

I'd like to make my table look like this by using the loan id to calculate individually how many participants and lead in each loan


loan_ID Firms Lender_role number of Part number of lead
1 A Participants 2 1
1 B Lead 2 1
1 C Participants 2 1

2 B Lead 0 1