SQL:- how to take Average of multiple(50) Columns with many null values in between, without listing 50 columns names in SQL?

how to take Average of multiple(50) Columns with many null values in between, without listing 50 columns names in SQL?I nee开发者_如何转开发d more general way of handling such situation?

It sounds like you would need to transpose your current table to achieve this. As far as I know, there is no way to transpose a MySQL table without specifying the exact columns to be transposed. So I would say that the best way would be to retrieve a row (or rows, depending on your needs) with SELECT * FROM table and then calculate the average using the whatever scripting / programming language you are using.

I would do it like this

select (isnull(col1,0)+isnull(col2,0)+...+isnull(col50,0))/50 as averageval
from table

but it is clear that your table design is wrong. You should have a values table that contains these values.

This is what a correct design would look like:

maintable
----------
id int

valuetable
----------
mainkey int -- foreign key to maintable.id
valuenum int -- may not be needed goes from 1 to 50
value int

At some point you will need to list the columns. Sql will not know which columns to include in the calculation until it is told.
If you calculation is a more then "just one time" consider placing a computed column in the table to hold the calculation which is maintained by SQL.