Unanswered: simulating median on table columns in SQLserver2000
I have a an sql table having 10,000 Rows.
The table has 5 columns. They are S1,S2,S3,S4 and M
I need the best way to calculate the MEDIAN for each row and store it in M. i.e. M=median(S1,S2,S3,S4)
I've come across articles where they've calculated medians on table rows, not table columns. But my requirement involves table columns. I guess transposing the columns into rows and then calculating median should be possible, but if I do that for 10000 rows using a cursor, then it would take a loooooong time.
While calculating the median on a set is not very difficult using TOP, calculating this for each row is quite different.
First you would need to swap the columns of each row in ascending order and then get the middle value. The later in your case is quite easy as it will be always the third column.
You should seriously consider doing this with your front-end.