(Serial Column) Filtering SQL Query and shortening the results to Range

Question

For example, i have column tables pallet_id and serial

I would like to query them from

pallet_id       serial 
PA1             161
PA1             163
PA1             164
PA1             165
PA1             166
PA1             177
PA1             178
PA1             179

to

pallet_id       serial 
PA1             161-161
PA1             163-166
PA1             177-179

serials are incremented by 1 needs to stick as a range group

I just using Views as of the moment. If there is another way to do this without resulting to stored procedure, please do advise.

I am developing the SP now because I can't find another way.

USING MSSQL2000 or lower.. 6.5? as it says here .. so no functions in this SQL Version. Upgrading is not an option too.

Answer 1

create table #T
(
  ID int identity,
  pallet_id varchar(8),
  serial int
)

insert into #T(pallet_id, serial)
select pallet_id, serial
from YourTable
order by pallet_id, serial

select pallet_id, min(serial) min_serial, max(serial) max_serial
from #T
group by pallet_id, serial - ID

drop table #T

SQL Fiddle