You are here: Re: Grouping numbers « MsSQL Server « IT news, forums, messages
Re: Grouping numbers

Posted by Steve Kass on 03/04/07 18:16

Erland Sommarskog wrote:

>
>
> I could suggest a query which in SQL 2005 at least give you a comma-
> separated list of the years. Collapsing adjacent years into ranges appears
> to make things a lot more complicated.

Here is a recursive solution that will do the job when
MAXRECURSION is no greater than the number of separate
years for one individual player. Some of the complication
is to get around limitations in what a recursive query
can contain (no GROUP BY, for example). The idea is
slippery, but not quite as messy as it looks.

CREATE TABLE T (
Pid INT,
yr INT,
primary key (Pid,yr)
)
go

INSERT T (Pid,yr) VALUES(1,1)
INSERT T (Pid,yr) VALUES(1,4)
INSERT T (Pid,yr) VALUES(1,3)
INSERT T (Pid,yr) VALUES(1,5)
INSERT T (Pid,yr) VALUES(1,6)
INSERT T (Pid,yr) VALUES(1,9)
INSERT T (Pid,yr) VALUES(1,10)
INSERT T (Pid,yr) VALUES(2,29)
INSERT T (Pid,yr) VALUES(2,30)
INSERT T (Pid,yr) VALUES(2,31)
INSERT T (Pid,yr) VALUES(2,9)
INSERT T (Pid,yr) VALUES(2,130)
INSERT T (Pid,yr) VALUES(2,131)
INSERT T (Pid,yr) VALUES(2,132)
go

with Mins(iter,Pid,lastwrite,lastfound,rowYr,yrs) as (
select
0,
Pid,
min(yr),
min(yr),
min(yr),
cast(min(yr) as varchar(max))
from T
group by Pid
union all
select
Mins.iter+1,
Mins.Pid,
case when min(T.yr) over (partition by Mins.Pid) = Mins.lastfound + 1
--and Mins.rightest < Mins.upto
then Mins.lastwrite else min(T.yr) over (partition by Mins.Pid) end,
min(T.yr) over (partition by Mins.Pid),
T.yr,
Mins.yrs
+ case when min(T.yr) over (partition by Mins.Pid) > Mins.lastfound + 1
then case when Mins.lastfound > Mins.lastwrite
then rtrim(Mins.lastfound) else '' end
+ ',' + rtrim(min(T.yr) over (partition by Mins.Pid))
else case when Mins.lastfound = Mins.lastwrite
then '-' else '' end
end
from Mins join T
on Mins.Pid = T.Pid
and Mins.lastfound < T.yr
and Mins.rowYr = Mins.lastfound
), AllSteps(Pid,yrs,lastwrite,lastfound,rk) as (
select distinct Pid, yrs,lastwrite,lastfound,
rank() over (partition by Pid order by iter desc)
from Mins
)
select
Pid,lastwrite,
yrs + case when lastwrite < lastfound then rtrim(lastfound) else ','+rtrim(lastfound) end
from AllSteps
where rk = 1

go

-- Steve Kass
-- Drew University
-- http://www.stevekass.com
-- 95508D54-0B01-431B-8B58-880146787216

 

Navigation:

[Reply to this message]


Удаленная работа для программистов  •  Как заработать на Google AdSense  •  England, UK  •  статьи на английском  •  PHP MySQL CMS Apache Oscommerce  •  Online Business Knowledge Base  •  DVD MP3 AVI MP4 players codecs conversion help
Home  •  Search  •  Site Map  •  Set as Homepage  •  Add to Favourites

Copyright © 2005-2006 Powered by Custom PHP Programming

Сайт изготовлен в Студии Валентина Петручека
изготовление и поддержка веб-сайтов, разработка программного обеспечения, поисковая оптимизация