Oracle SQL - Set consecutive numbers as a range

Question

This query returns the following results...

WITH t(wk, COST) AS
(SELECT wk, COST FROM myTable WHERE id = '345')
SELECT listagg(wk,',') WITHIN GROUP(ORDER BY wk) AS wks, COST 
FROM   t
GROUP BY COST;

...

WKS               COST
----------------------
17, 18, 19, 21    446
26, 27, 28        588

Is it possible to create the following results where consecutive weeks are returned as from and to. (eg 1-10 rather than 1, 2, 3, 4,... etc)

WKS               COST
----------------------
17-19, 21         446
26-28             588

shouldn't the second line be '26-28' ?

Radu Gheorghiu
– Radu Gheorghiu

2013-10-09 10:59:30 +00:00
Commented Oct 9, 2013 at 10:59 — Radu Gheorghiu
– Radu Gheorghiu, Commented Oct 9, 2013 at 10:59
Good spot! I'll change that...

Tom
– Tom

2013-10-09 11:01:20 +00:00
Commented Oct 9, 2013 at 11:01 — Tom
– Tom, Commented Oct 9, 2013 at 11:01

Noel · Accepted Answer · 2013-10-09 11:32:24Z

3

In this problem you have to identify the sequence of consecutive weeks and group them. Here's my solution.

Use the LAG function to identify any breaks in the sequence.
Use the SUM function to assign a group number to each sequence.
Find the starting and ending week in each group.
Finally use the LISTAGG function to aggregate the result.

Query:

with x(wk, cost, startgroup) as(
    --identify the start of a sequence
    select wk, cost,
           case when wk = lag(wk,1) over (partition by cost order by wk) + 1
                then 0
                else 1
             end
    from mytable
    where id = '345'
    ),
    y(wk, cost, grp) as(
    --assign group number
    select wk, cost,
           sum(startgroup) over (partition by cost order by wk)
    from x
    ),
    z(wk, cost, grp) as(
    --get the max/min week for each group
    select case when min(wk) = max(wk)
                then cast(min(wk) as varchar2(10))
                else min(wk) ||'-'||max(wk)
                end,
            cost, grp
    from y
    group by cost, grp
    )
--aggregate by cost
select listagg(wk,',') within group(order by grp),
cost
from z
group by cost;

Demo at sqlfiddle.

answered Oct 9, 2013 at 11:32

Noel

10.6k30 gold badges53 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tom Over a year ago

Gonna take a while to get my head around this, but it's exactly what I'm looking for... thanks!

Collectives™ on Stack Overflow

Oracle SQL - Set consecutive numbers as a range

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related