I'm working on a project where I need to cycle a flag amongst a set of columns. To achieve this I am storing a position value in each column which allows me to cycle them.i.e with columns a to f I have the values as followsa b c d e f

1 2 0 3 4 0

Note that c and f are 0 because they are not included in the cycle.

In one process I need to know how many columns are non zero.

So the challenge is to find out the how many non zero columns there are, the twist is to use as little code as possible. Oh and scalar user defined functions are out because TSQL Scalar functions are evil.

Please post answers as comments or email to me using the contact page.

[Note: this needs to be usuable as a computed column. The non zero values will always be in consequtive list of values from 1)

–