Grouping Logic

[VVVA]

This seems like it should be simple, but I can't come up with a simple solution.

I want to turn this

Book Type |Book

Deed Record 1
Deed Record 2
Deed Record 3
Deed Record 5
Mortgage Record 1

Into

Book Type |Book

Deed Record 1 - 3
Deed Record 5
Mortgage Record 1

Any ideas?

 

[jbenson001]

Formatting of data should be done on the front end, not with SQL. SQL is for data manipulation.

 

[VVVA]

Yeah, I know. Problem is the front end doesn't allow formatting like it should, so I'm stuck with SQL.

For anybody coming along who is interested doing something like this, this really nasty query seems to work.

select 
 BookType,
 min(book) as BegBook,
 max(book) as EndBook

from
(
select
 a.BookType,
 a.book,
 isnull(b.book,0)+1 as control

from object a left outer join object b on 

a.BookType=b.BookType and
cast(a.book as int)=cast(b.book as int)+1 and
b.typ=158

where
 a.typ=158
) as a
group by
 BookType,
 case when book=control then 1 else book end