How to get median values when using WITH CUBE?

In a nutshell: when using WITH CUBE in MSSQL 2005, is there any way of directly calculating median values, preferably without using loops?

Some code:

CREATE TABLE #Example (
    ID1 INT,
    ID2 INT,
    [Value] INT
)

INSERT #Example(ID1,ID2,[Value])
    SELECT 1, 1, 45 UNION
    SELECT 1, 2, 78 UNION
    SELECT 1, 3, 23 UNION
    SELECT 1, 4, 119 UNION
    SELECT 1, 5, 85 UNION
    SELECT 2, 1, 143 UNION
    SELECT 2, 2, 124 UNION
    SELECT 2, 3, 161 UNION
    SELECT 2, 4, 171 UNION
    SELECT 2, 5, 142

SELECT ID1, ID2, COUNT(1) AS 'NumValues', MIN([Value]) AS 'Minimum', AVG([Value]) AS 'Average', MAX([Value]) AS 'Maximum'
    F开发者_如何学JAVAROM #Example
    GROUP BY ID1, ID2
    WITH CUBE
    HAVING ID1 IS NULL OR ID2 IS NULL
    ORDER BY ISNULL(ID1, 0), ISNULL(ID2, 0)

DROP TABLE #Example

How can I get a column for median values?

The best I've managed so far is to loop over the ID1 and ID2 values, and calculate a median for each set of IDs, but that seems inefficient and ugly.

Any ideas gratefully received!

Somewhat belatedly, I think that this would work:

select * from
(
    SELECT ID1, ID2, COUNT(1) AS 'NumValues', MIN([Value]) AS 'Minimum', AVG([Value]) AS 'Average', MAX([Value]) AS 'Maximum'
    FROM #Example
    GROUP BY ID1, ID2
    WITH CUBE
    HAVING ID1 IS NULL OR ID2 IS NULL
) T
OUTER APPLY 
(
    select avg(val) as median from
    (
        select [value] as val, row_number() over (order by [value]) as rn
        from #Example E 
        where (T.ID1=E.ID1 or T.ID1 is null) and (T.ID2=E.ID2 or T.ID2 is null) 
    ) I
    where abs(rn - (T.NumValues+1)/2.0) <1
) T2