Summarise null rows in Oracle

Question

I have a dataset like this:

+---------------+-------+
| SAMPLE_NUMBER | SCORE |
+---------------+-------+
|             1 | 100   |
|             2 | 97    |
|             3 | 124   |
|             4 | 762   |
|             5 | 999   |
|             6 | 1200  |
|             7 | NULL  |
|             8 | NULL  |
|             9 | NULL  |
|            10 | NULL  |
+---------------+-------+

I want to be able to summarise the NULL rows instead of displaying them all. So ideally, I would want the above to look like this:

+---------------+-------+
| SAMPLE_NUMBER | SCORE |
+---------------+-------+
| 1             | 100   |
| 2             | 97    |
| 3             | 124   |
| 4             | 762   |
| 5             | 999   |
| 6             | 1200  |
| 7-10          | NULL  |
+---------------+-------+

Is there any way in Oracle to do this? Or is it something I will have to do post-query?

Answer 1

Yes. For your sample data:

select (case when score is null then min(sample_number) || '-' || max(sample_number)
             else min(sample_number)
        end) as sample_number,
       score
from table t
group by score
order by min(id)

In other words, group by score and then fiddle with the sample number. Note: this assumes that you do not have duplicate scores. If you do, you can do so with a more complicated version:

select (case when score is null then min(sample_number) || '-' || max(sample_number)
             else min(sample_number)
        end) as sample_number,
       score
from (select t.*,
             row_number() over (partition by score order by sample_number) as seqnum
      from table t
     ) t
group by score, (case when score is not null then seqnum end);