How to isolate single values from multiple records

Question

Hello ladies and gentlemen.

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

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

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

Tom

Answer 1

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,
       beta_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

Answer 2

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,
       beta_code,
       decode(lag(value, 1) over (partition by alpha_code order by beta_code), 
          null, value, null) value
  from the_table