asked on

List of Numbers to Single String of Ranges

I am tasked with taking a list of numbers and creating a single string of ranges. Lets say I do a query on a single column in a table and the resulting list is as follows:

333001
333002
333003
333004
333007
333008
333010

Does anyone know of an easy way to return a single string of ranges like this:

333001-333004,333007-333008,333010? There could be 0 to n numbers.

slightwv (䄆 Netminder)

My guess is that there probalby isn't a generic way to pull this off.

This question was posted for two different databases. Can you clarify if you need this for both?

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Sean Stuber

listagg requires 11gR2 if you only have 10g then try xml aggregation, uglier syntax but same idea

SELECT RTRIM(
EXTRACT(
XMLAGG(
XMLELEMENT(
"x",
CASE
WHEN MIN(n) != MAX(n) THEN MIN(n) || '-' || MAX(n)
ELSE TO_CHAR(MIN(n))
END
|| ',')
ORDER BY MIN(n)),
'/x/text()').getstringval(),
',')
FROM (SELECT n,
LAST_VALUE(CASE WHEN prev != n - 1 THEN prev END IGNORE NULLS) OVER (ORDER BY n) x
FROM (SELECT n, NVL(LAG(n) OVER (ORDER BY n), n) prev FROM x))
GROUP BY x

GGF-IT

ASKER

Awesome, thanks! Found one issue if the first number is alone 1,5,6,7. It would list 1-7. Just changed

NVL(LAG(n) OVER (ORDER BY n), n) prev

to be
case when rownum = 1 then 0 else
NVL(LAG(n) OVER (ORDER BY n), n) end prev

and it is exactly what I needed!

GGF-IT

ASKER

Sorry if I was unclear on the database. The database is Oracle, but connecting to it using MS SQL Reporting Services.

Sean Stuber

even easier fix

change

NVL(LAG(n) OVER (ORDER BY n), n) prev

to

NVL(LAG(n) OVER (ORDER BY n), 0) prev

GGF-IT

ASKER

That makes much more sense - thanks!