You’ll need MS SQL 2005 or above, it looks like version 2012 has it built in see here!

This is my first use of the OVER bit so it may be a little ugly, also I have been using the old way to pivot stuff so it’s a bit messy at the end.

```
declare @t table (percentile numeric(10,5))
insert into @t values (0)
insert into @t values (0.25)
insert into @t values (0.5)
insert into @t values (0.75)
insert into @t values (0.8)
insert into @t values (1.0)
--give rownumbers for your data over a partition
drop table ##mgw
SELECT
s.gppraccode
,age
,ROW_NUMBER() OVER (partition by gppraccode ORDER BY age) AS "RowNumber"
INTO ##mgw
FROM mgw_ane_base AS s
--calculate the individual values per percentile
drop table ##mgw_percentileHolding
select t.percentile*(max(rownumber)-1)+1 as act
,floor(t.percentile*(max(rownumber)-1)+1) as K
,((t.percentile*(max(rownumber)-1)+1) % 1) as D
,count(*) as N
,t.percentile
,a.gppraccode
into ##mgw_percentileHolding
from ##mgw as a join @t as t on 1=1
group by t.percentile,a.gppraccode
--fold it all over in a manual-ish pivot
select
gppraccode
,max(case when percentile=0 then PercentileValue end) as [0%]
,max(case when percentile=0.25 then PercentileValue end) as [25%]
,max(case when percentile=0.5 then PercentileValue end) as [50%]
,max(case when percentile=0.75 then PercentileValue end) as [75%]
,max(case when percentile=0.8 then PercentileValue end) as [80%]
,max(case when percentile=1 then PercentileValue end) as [100%]
,N
FROM
(
SELECT
*
,(SELECT max(age) from ##mgw as b where b.rownumber<=a.act and a.gppraccode=b.gppraccode) as below
,(SELECT min(age) from ##mgw as b where b.rownumber>=a.act and a.gppraccode=b.gppraccode) as above
,(SELECT min(age) from ##mgw as b where b.rownumber>=a.act and a.gppraccode=b.gppraccode)-(select max(age) from ##mgw as b where b.rownumber<=a.act and a.gppraccode=b.gppraccode ) as f
,case when percentile=0 then (SELECT min(age) from ##mgw as b where a.gppraccode=b.gppraccode)
when percentile=1 then (SELECT max(age) from ##mgw as b where a.gppraccode=b.gppraccode)
else (SELECT max(age) from ##mgw as b where b.rownumber<=a.act and a.gppraccode=b.gppraccode)+D*((select min(age) from ##mgw as b where b.rownumber>=a.act and a.gppraccode=b.gppraccode)-(select max(age) from ##mgw as b where b.rownumber<=a.act and a.gppraccode=b.gppraccode)) end as PercentileValue
FROM ##mgw_percentileHolding as a
) as internal
group by gppraccode,N
```