1

I am trying to have my results be in ranges (e: xx - xx) instead of having each number listed (ex: XX, XX, XX, XX)

This is the table I used for my example

TABLE

Doc   Pages
ABC   1
ABC   2
ABC   3
ABC   4
ABC   5
ABC   6
ABC   7
TEST  30
TEST  31
TEST  35
TEST  36
TEST  37
TEST  38
TEST  39
TEST  41
EFG   50
EFG   51
EFG   52
EFG   55

This is the function I used to get my results

FUNCTION

CREATE FUNCTION [dbo].[GetPagebyDoc]

(
@Doc varchar(50)
)

RETURNS varchar(max)
AS

BEGIN

      declare @output varchar(max)

      select @output = COALESCE(@output + ', ',' ') + pages
      from TABLE
      where doc = @doc

      return @output

END
GO

SELECT doc, dbo.GetPagebyDoc(doc)
FROM TABLE 
GROUP BY doc
GO

This is my results from the function

RESULTS

Doc   Pages
ABC   1, 2, 3, 4, 5, 6, 7
TEST  30, 31, 35, 36, 37, 38, 39, 41
EFG   50, 51, 52, 55

I need help to get my results to look like this.

I WOULD LIKE MY RESULTS TO BE

Doc   Pages
ABC   1 - 7
TEST  30 - 31, 35 - 39, 41
EFG   50 - 52, 55
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
user3536826
  • 11
  • 3
  • Either the table, or the function, or the results are not represented here correctly, since you could not get the results you're saying with that table and that function. – ErikE May 09 '14 at 00:36
  • You contradict yourself, you say "I am trying to have my results be in ranges (e: xx - xx) instead of having each number listed" but then say "However, I would like my results to be: ABC 1, 2, 3, 4, 5, 6, 7". Which way do you want to present the values? – Tony May 09 '14 at 00:50
  • I have it mixed up in this question. – user3536826 May 09 '14 at 00:52
  • I had made corrections and edits. It is now properly stating what I need. – user3536826 May 09 '14 at 00:55
  • I assume it is SQL Server. What version of it are you using? – cha May 09 '14 at 01:08
  • Added sql-server tag based on the syntax of the code sample. – Gordon Linoff May 09 '14 at 02:31

1 Answers1

0

You are trying to identify Islands and Gaps in Sequential Numbers, so using the example found on the linked page one solution is:

SELECT t1.Doc, t1.Pages as startOfGroup, MIN(t2.Pages) as endOfGroup
FROM (SELECT Doc, Pages FROM Table1 tbl1 
  WHERE NOT EXISTS(SELECT * FROM Table1 tbl2 
    WHERE tbl1.Pages - tbl2.Pages = 1)) t1
  INNER JOIN (SELECT Doc, Pages FROM Table1 tbl1 
  WHERE NOT EXISTS(SELECT * FROM Table1 tbl2 
    WHERE tbl2.Pages - tbl1.Pages = 1)) t2
  ON t1.Pages <= t2.Pages
GROUP BY t1.Doc, t1.Pages

SQL Fiddle

To get the result to look as you describe I would get the presentation/application layer to reformat the output.

Tony
  • 9,672
  • 3
  • 47
  • 75
  • I tried and get this error "Operand data type varchar(max) is invalid for subtract operator." – user3536826 May 12 '14 at 16:40
  • I change varchar to int and reran the query. That query does not give me the results I am expecting. – user3536826 May 12 '14 at 19:09
  • Did you look at the [SQL Fiddle](http://sqlfiddle.com/#!3/15d2f/9) I created from your source data? – Tony May 12 '14 at 19:39
  • I've updated the [SQL Fiddle](http://sqlfiddle.com/#!3/15d2f/23) to get closer to the output you describe but personally I would leave it up to the application/display layer to reformat the data. – Tony May 12 '14 at 19:55
  • I'm not familiar with SQL Fiddle. – user3536826 May 23 '14 at 22:54
  • [SQL Fiddle](http://sqlfiddle.com/about.html) allows you to "Build a representative database (schema and data) and post a link to it..." so in this case I can build a query and share it with you. To test it out, copy the query from here (http://sqlfiddle.com/#!3/15d2f/23) to your database. – Tony May 26 '14 at 21:12