Create Range form integers (Numbers)

Question

Post reply

Create Range form integers (Numbers)

anilp

SSC Enthusiast

Points: 103
More actions
October 14, 2010 at 8:51 am

#227849

Hi,
I have integer value column in table. these values are not sequential. Some values may be missing in sequence.
i.e.
document_id
------------
1
2
3
6
45
46
47
49
100
101
102
......etc.
I have to generate range like for above case
1-3,6,45-47,49,100-102 ......
mean numbers are in sequence then minimum number - maximum number of sequence otherwise number itself by comma seperated.
What is optimum way i can generate for millians of records in table.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

This type of issue was recently solved on this forum. Please try this solution, and see if this solves it for you also.

Please let us know how this works for you, and if you need anything else in this.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Ken McKelvey SSCoach Points: 18843 More actions · Answer 2

You can use the group by order difference technique to get the ranges followed by the stuff xml technique to roll them up.

-- *** Test data in comsumable format ***

-- Please provide this in future

CREATE TABLE #t

(

document_id int NOT NULL

)

INSERT INTO #t

SELECT 1

UNION ALL SELECT 2

UNION ALL SELECT 3

UNION ALL SELECT 6

UNION ALL SELECT 45

UNION ALL SELECT 46

UNION ALL SELECT 47

UNION ALL SELECT 49

UNION ALL SELECT 100

UNION ALL SELECT 101

UNION ALL SELECT 102

-- *** End Test data in comsumable format ***

;WITH OD

AS

(

SELECT document_id

,document_id - ROW_NUMBER() OVER (ORDER BY document_id) AS OrderDiff

FROM #t

)

,ODGroup

AS

(

SELECT OrderDiff

,MIN(document_id) AS MinID

,MAX(document_id) AS MaxID

FROM OD

GROUP BY OrderDiff

)

SELECT STUFF

(

SELECT ' ' + CAST(MinID AS varchar(20))

+ CASE WHEN MinID = MaxID THEN '' ELSE '-' + CAST(MaxID AS varchar(20)) END + ','

FROM ODGroup

FOR XML PATH('')

)

,1

,''

) AS List

WayneS SSC Guru Points: 95460 More actions · Answer 3

One of these should get you going down the right track.

if OBJECT_ID('tempdb..#Documents') IS NOT NULL DROP TABLE #Documents;

CREATE TABLE #Documents (document_id int PRIMARY KEY CLUSTERED);

INSERT INTO #Documents

SELECT 1 UNION ALL

SELECT 2 UNION ALL

SELECT 3 UNION ALL

SELECT 6 UNION ALL

SELECT 45 UNION ALL

SELECT 46 UNION ALL

SELECT 47 UNION ALL

SELECT 49 UNION ALL

SELECT 100 UNION ALL

SELECT 101 UNION ALL

SELECT 102;

-- get the results - show gaps...

SELECT GapStart = ISNULL((SELECT MAX(lo.document_id+1)

FROM #Documents lo

WHERE lo.document_id < hi.document_id),1),

GapEnd = hi.document_id - 1

FROM #Documents hi

WHERE hi.document_id NOT IN (SELECT document_id + 1

FROM #Documents g1 )

AND hi.document_id > 1;

-- get the results - show islands...

SELECT start_range = MIN(document_id),

end_range = MAX(document_id)

FROM (SELECT document_id,

grp = document_id - ROW_NUMBER() OVER (ORDER BY document_id)

FROM #Documents) r

GROUP BY grp;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

anilp SSC Enthusiast Points: 103 More actions · Answer 4

anilp

SSC Enthusiast

Points: 103

October 15, 2010 at 7:23 am

#1237515

Thanks WayneS

i can complete it.

WayneS SSC Guru Points: 95460 More actions · Answer 5

You're welcome. Come on back if you have any more questions!

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2