CODEWITHSUNDEEP
sqloracle-database
marderh
marderh

Reputation: 1256

SQL: Concatenate sequential integer values

I have a column like this:

ID
--------
1
2
3
4
5
7
10

and I want to get the following resultset:

ID
--------
1-5
7
10

Is there a way to achieve this with (Oracle) SQL only?

Upvotes: 2

Views: 728

Answers (2)

Charles Bretana
Charles Bretana

Reputation: 146499

self joins are necessary... I think this will work

Select a.id, b.id
From table a   -- to get beginning of each range
  Join table b -- to get end of each range
    On b.id >= a.Id -- guarantees that b is after a
       And Not exists (Select * From table m  -- this guarantees all values between 
                       where id Between a.id+1 and b.id
                           And Not exists(Select * From table 
                                          Where id = m.id-1))
       And Not exists (Select * From table  -- this guarantees that table a is start 
                       Where id = a.id -1)
       And Not exists (Select * From table  -- this guarantees that table b is end
                       Where id = b.id + 1)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Yes:

select (case when min(id) < max(id)
             then cast(min(id) as varchar2(255)) || '-' || cast(max(id) as varchar2(255))
             else cast(min(id) as varchar2(255))
        end)
from (select id, id - rownum as grp
      from t
      order by id
     ) t
group by grp
order by min(id);

Here is a SQL Fiddle demonstrating it.

The idea behind the query is that subtracting rownum from a sequence of numbers results in a constant. You can use the constant for grouping.

Upvotes: 7

Related Questions