for each loop in oracle SQL With dynamic columns to display

Question

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.

Ori Marko · Accepted Answer · 2018-06-19 08:25:14Z

1

You can add such calculation in select:

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

answered Jun 19, 2018 at 8:25

Ori Marko

59.3k26 gold badges157 silver badges259 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ayub H Over a year ago

Thank you very much for your response, but I had to edit the result output for the change in requirements, It would be a great help if you could suggest something

Ori Marko Over a year ago

@AyubH it's a different question, can you ask a new question?

Ayub H Over a year ago

I have reached the Question limit, it is bit urgent can u suggest me something I can do

Collectives™ on Stack Overflow

for each loop in oracle SQL With dynamic columns to display

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related