0

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

Here I am not pivoting the rows into columns, we are supposed to multiply the value in column with some number and display as value in a new column with a new new to column as shown in sample output

Thanks in advance for the help.

Ayub H
  • 1
  • 6
  • 2
    Duplicate of [Dynamic pivot in oracle sql](https://stackoverflow.com/questions/15491661/dynamic-pivot-in-oracle-sql) – Kaushik Nayak Jul 05 '18 at 08:16
  • It is not duplicate of Dynamic pivot in oracle sql, because I am not just pivoting the rows into columns, Here we are supposed to multiply the value of column with some number as display as the column with new column name. see my sample output, the value of percentage is multiplied by 25 and displayed value of that in a new column RESULT1........N. – Ayub H Jul 05 '18 at 09:41
  • So, multiply your percentage columns by the necessary value *before* doing the pivot? – Boneist Jul 05 '18 at 10:23
  • Thanks for your suggestion can you please edit my code for this? I am not getting this as am completely new to Oracle and sql – Ayub H Jul 05 '18 at 10:29

1 Answers1

1

Yes, you are pivoting. You want data from five rows into one row and five columns, multiplying is not important. For 5 columns you can use below query (or case when equivalent in older Oracle versions):

select * 
  from (select groupid, percentage, 
               row_number() over (partition by groupid order by null) rn 
          from your_query)
  pivot (sum(percentage * 25) as result for rn in (1, 2, 3, 4, 5))

sqlfiddle demo

For dynamic number of columns you need solution from suggested duplicate question, and there are many similar ones on SO.

In case when you can also handle situation where there are more than 5 values, add one column summing 6+ or add info text.

Ponder Stibbons
  • 14,723
  • 2
  • 21
  • 24
  • Thank you @Ponder Stibbons for your help i was waiting for the confirmation from our client. this helped solve my issue 80%, But here my problem is we are not fixed only with 5 values this keeps changing record by record, i want those values dynamically updated in my output Please suggest how do I make this dynamic I don't want only 5 values, please – Ayub H Jul 17 '18 at 09:52
  • Hi. Glad I could help somehow. There's no simple way for dynamic number of columns. Oracle "needs to know" generated columns beforehand. The only solutions I've seen are these proposed in similar questions, just look for "Oracle dynamic pivot", maybe you will be able to adapt one. – Ponder Stibbons Jul 17 '18 at 14:26