Is there any way in Oracle to skip a value in a select when it has been already shown?-CodePudding

Sorry because maybe it's a silly question but honestly I don't know how to handle it. Let's imagine that after executing a select with a group by clausule I have the following information:

col1    col2    col3
value1  optionA  1
value1  optionB  5
value2  optionA  3
value2  optionB  4

But I would like to obtain the following:

col1    col2    col3
value1  optionA  1
        optionB  5
value2  optionA  3
        optionB  4

In other words, I don't want to show a previously shown value if it's the same (in this case, value1 and value2)

Is this feasible with Oracle? if the answer if yes, would you kindly point me in the right direction?

Thank you.

CodePudding user response：

You don't say what's the ordering criteria for the rows so I'll assume it's (col1, col2).

The query you want should look like:

select
  case when col1 = lag(col1) over(order by col1, col2) 
       then null
       else col1
  end as col1,
  col2, 
  col3
from t

CodePudding user response：

If you were on SQL*Plus and used Oracle version which doesn't support analytic functions, you could have used its break command. I guess that's not the case nowadays, eh?

Originally:

SQL> with temp (col1, col2, col3) as
  2    (select 'value1', 'optionA', 1 from dual union all
  3     select 'value1', 'optionB', 5 from dual union all
  4     select 'value2', 'optionA', 3 from dual union all
  5     select 'value2', 'optionB', 4 from dual
  6    )
  7  select col1, col2, col3
  8  from temp
  9  order by col1, col2;

COL1   COL2          COL3
------ ------- ----------
value1 optionA          1
value1 optionB          5
value2 optionA          3
value2 optionB          4

Let's break it:

SQL> break on col1
SQL>
SQL> /

COL1   COL2          COL3
------ ------- ----------
value1 optionA          1
       optionB          5
value2 optionA          3
       optionB          4

SQL>