Calculating the Median Across Columns of a Table

Question

I need to calculate the median across column values of a table that include various age ranges, and I'm having trouble finding the answer to my problem. The table is structured as follows:

ID    0-5    5-10    11-15    16-20   ...
------------------------------------------
1     14     5       12       5       ...
2     5      11      14       17      ...

I've read about finding the median of data, using rowcount and aggregates, but that seems to require the data all be in the same column. Because of this, I think I need to use the PIVOT function within SQL, but I can't figure out the syntax needed. Can anyone help me with this?

Answer 1

There is no method that is really elegant. I would go for brute force:

select id,
       (case when tot1 >= totn / 2 then 'col1'
             when tot2 >= totn / 2 then 'col2'
        . . .
       ) as mediancolumn
from (select t.*,
             col1 as Tot1,
             col1 + col2 as Tot2,
             . . .
             (col1 + col2 + . . . coln) as Totn
      from t
     ) t

If you actually want to parse the ranges, then the same idea holds. But you need to change the then part of the case statement to handle the numeric values.