CODEWITHSUNDEEP
sqloracle-database
Ayub H
Ayub H

Reputation: 1

for each loop in oracle SQL With dynamic columns to display

I am new to SQL please help me resolving the query as below

SELECT DISTINCT
       ROG.GROUPID,
       CAPA1.PERC,
       CAPA1.TPTID
FROM AGREGATEDPOLICY APO
INNER JOIN REINSURANCEOPERATION RO ON APO.AGREGATEDPOLICYID = RO.AGREGATEDPOLICYID
INNER JOIN REINSURANCEOPERATIONGROUPINFO ROG ON RO.OPERATIONID = ROG.OPERATIONID
INNER JOIN (SELECT RIC.GROUPID GID, RIP.PERCENTAGE PERC, RIP.THIRDPARTYID TPTID
              FROM RI_REINSURANCECONTRACT RIC
        INNER JOIN RI_CONTRACTCOMPONENT RICC ON RICC.MAINID = RIC.RCID
        INNER JOIN RI_CCPARTICIPATION RICP ON RICP.CCID = RICC.CCID
        INNER JOIN RI_PARTICIPATION RIP ON RIP.PARTICIPATIONID = RICP.PARTICIPATIONID
        INNER JOIN RI_EXCESSLOSS RIE ON RIE.EXCESSLOSSID = RICC.CCID
        INNER JOIN EXCESOPERDIDA EXP ON EXP.PK = RIE.EXCESSDCOID
             WHERE RIC.REINSURANCECONTRACTTYPE = 0
               AND RICC.TYPE = 'LossExcess') CAPA1 ON ROG.GROUPID = CAPA1.GID

Now there might be more than one THIRDPARTYID for each GROUPID. I want to multiply 25 with the PERCENTAGE of every THIRDPARTYID.

For example if the output is

GROUPID PERCENTAGE THIRDPARTYID
2824    0.4        824603
2824    0.4        824500
2824    0.5        824603
2824    0.2        824601
2824    0.5        824500

I want to display the output as RESULT1.....n (Dynamically change the n depending on the THIRDPARTYID like below

GROUPID  RESULT1  RESULT2  RESULT3  RESULT4  RESULT5
2824     10       10       12.5     5        12.5

Thanks in advance for the help.

Upvotes: 0

Views: 59

Answers (1)

Ori Marko
Ori Marko

Reputation: 58782

You can add such calculation in select:

SELECT DISTINCT
       ROG.GROUPID,
       CAPA1.PERC,
       CAPA1.TPTID,CAPA1.PERC*25 as RESULT

Upvotes: 1

Related Questions