sql server - Select Top n Values Across Columns and Average -

i have table has 13 columns, 1 type varchar(25) , rest type `int (holding values each month of year).

for each row, pick top 6 int values 12 columns , calculate average of values.

i know how select top n given column, how do across multiple columns?

select id,        (        select avg(c)        (             select top(6) c             (values(c1),(c2),(c3),(c4),(c5),(c6),(c7),                         (c8),(c9),(c10),(c11),(c12)) t(c)             order c desc             ) t        ) c yourtable 

sql fiddle

for sql server 2005 since can't use table value constructor

select id,        (        select avg(c)        (             select top(6) c             (select c1 union                   select c2 union                   select c3 union                   select c4 union                   select c5 union                   select c6 union                   select c7 union                   select c8 union                   select c9 union                   select c10 union                   select c11 union                   select c12) t(c)             order c desc             ) t        ) c yourtable 

sql fiddle

and sql server 2000 work you.

select t1.id,        avg(c) c (      select id, c1 c yourtable union      select id, c2  yourtable union      select id, c3  yourtable union      select id, c4  yourtable union      select id, c5  yourtable union      select id, c6  yourtable union      select id, c7  yourtable union      select id, c8  yourtable union      select id, c9  yourtable union      select id, c10 yourtable union      select id, c11 yourtable union      select id, c12 yourtable      ) t1 (       select count(*)       (            select id, c1 c yourtable union            select id, c2  yourtable union            select id, c3  yourtable union            select id, c4  yourtable union            select id, c5  yourtable union            select id, c6  yourtable union            select id, c7  yourtable union            select id, c8  yourtable union            select id, c9  yourtable union            select id, c10 yourtable union            select id, c11 yourtable union            select id, c12 yourtable            ) t2       t1.id = t2.id ,             t1.c <= t2.c       ) <= 6 group t1.id 

sql fiddle

i not expect particularly fast. perhaps better option store intermediate result in temp table.

create table #t (   id varchar(25),   c int )  insert #t select id, c1 c yourtable union select id, c2  yourtable union select id, c3  yourtable union select id, c4  yourtable union select id, c5  yourtable union select id, c6  yourtable union select id, c7  yourtable union select id, c8  yourtable union select id, c9  yourtable union select id, c10 yourtable union select id, c11 yourtable union select id, c12 yourtable  select t1.id,        avg(c) c #t t1 (       select count(*)       #t t2       t1.id = t2.id ,             t1.c <= t2.c       ) <=  6  group t1.id    drop table #t