Obtain a concise string of the numbers, for example, given 1,2,3,6,7,8,9,12,14, we expect 1-3,6-9,12,14.

Here is the Table:

create table tt8 (c1 numeric); insert into tt8 values (1),(2),(3),(6),(7),(8),(9),(12),(14);

Use Table tt8, Here is what the result should look like:

numbers --------------- 1-3,6-9,12,14

Here is what I have so far, but it’s giving me type error. I don’t think this is the correct way.

select c1, case when c1 = 1|2|3 then '1-3' when c1 = 6|7|8|9 then '6-9' else c1 end from tt8;

## Advertisement

## Answer

You can use a gaps-and-islands approach and then aggregation. The following gets the groups:

select min(c1) || (case when count(*) = 1 then '' else '-' || max(c1) end) from (select tt8.*, row_number() over (order by c1) as seqnum from tt8 ) t group by (c1 - seqnum);

You can then put them into a single string:

select string_agg(val, ',' order by min_c1) from (select min(c1) || (case when count(*) = 1 then '' else '-' || max(c1) end) as val, min(c1) as min_c1 from (select tt8.*, row_number() over (order by c1) as seqnum from tt8 ) t group by (c1 - seqnum) ) t;

Here is a db<>fiddle.