Hello ladies and gentlemen.

If we have the following records (as example) from a query:

enter image description here

is it possible to present the following sql result with pl/sql oracle query ?

enter image description here

We need to have the single ALPHA CODE record, the analytic BETA CODE record related to the alpha code from the first query, and SUM of the VALUE record = 30 and not 90 (3 x 30).

Thank you very much for your help in advance.

Best regards



This kind of formatting should be usually done in the front end, not in SQL.

You can however get the display you want using window functions, no PL/SQL required.

select case when rn = 1 then alpha_code end as alpha_code,
       case when rn = 1 then value end as value
from (
  select alpha_code, beta_code, value, 
         row_number() over (partition by alpha_code order by beta_code) as rn
  from the_table
order by beta_code;

SQLFiddle example: http://sqlfiddle.com/#!4/28ca3/1


Another one variant with lag (for the sqlfiddle from an a_horse_with_no_name's answer):

select decode(lag(alpha_code, 1) over (partition by alpha_code order by beta_code), 
              null, alpha_code, null) alpha_code,
       decode(lag(value, 1) over (partition by alpha_code order by beta_code), 
          null, value, null) value
  from the_table

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.