|
|
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
[Back to original message]
|