TSQL Challenge - counting non zero columns

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)

 


-
Published 07 August 2009 13:13 by simonsabin

Comments

07 August 2009 14:13 by simonsabin

# re: TSQL Challenge - counting non null columns

From email from Mladen Prajdic

here you go.

I assumed you know the number of columns :)

I also just put the 0 in random columns

DECLARE @t TABLE (a INT, b INT, c INT, d INT, e INT, f INT)

INSERT INTO @t

SELECT 1, 2, 0, 3, 4, 0 union all SELECT 0, 2, 0, 3, 0, 0 union all SELECT 1, 2, 0, 0, 4, 0 union all SELECT 0, 0, 0, 0, 0, 0

SELECT  RN, ABS(SUM(SIGN(a)-1 + SIGN(b)-1 + SIGN(c)-1 + SIGN(d)-1 + SIGN(e)-1 +SIGN(f)-1)) as NumberOfZeros

FROM    (SELECT  ROW_NUMBER() over(order by (select 1)) as RN, *

          FROM    @t) t

          group by RN

07 August 2009 15:03 by Madhivanan

# re: TSQL Challenge - counting non null columns

This will work for any number of columns

create table testing (a int,b int,c int,d int,e int,f int)

insert into testing

select 1 ,2 ,0 ,3 ,4 ,0 union all

select 1 ,3 ,0 ,0 ,0 ,0 union all

select 0 ,0 ,0 ,0 ,2 ,0

declare @s varchar(8000)

set @s=''

select

@s=@s+'case when '+COLUMN_NAME+'<>0 then 1 else 0 end+'

from

INFORMATION_SCHEMA.COLUMNS

where

TABLE_NAME='testing'

set @s=left(@s,len(@s)-1)

set @s='select row_number() over(order by (select 1)) as row_number,'+@s+' as non_nulls from testing '

exec(@s)

07 August 2009 15:03 by Madhivanan

# re: TSQL Challenge - counting non null columns

This will work for any number of columns

create table testing (a int,b int,c int,d int,e int,f int)

insert into testing

select 1 ,2 ,0 ,3 ,4 ,0 union all

select 1 ,3 ,0 ,0 ,0 ,0 union all

select 0 ,0 ,0 ,0 ,2 ,0

declare @s varchar(8000)

set @s=''

select

@s=@s+'case when '+COLUMN_NAME+'<>0 then 1 else 0 end+'

from

INFORMATION_SCHEMA.COLUMNS

where

TABLE_NAME='testing'

set @s=left(@s,len(@s)-1)

set @s='select row_number() over(order by (select 1)) as row_number,'+@s+' as non_nulls from testing '

exec(@s)

07 August 2009 16:58 by Theo Spears

# re: TSQL Challenge - counting non null columns

If you takie advantage of the additional information provided (the non-zero columns consist  of values 1...n for n columns) you can reduce the solution to:

SELECT FLOOR(SQRT(2*(a+b+c+d+e+f))) FROM yourTable

This is, arguably, a dirty hack.

07 August 2009 17:12 by simonsabin

# re: TSQL Challenge - counting non null columns

A revised one from Mladen

SIGN(a)+SIGN(b)+SIGN(c)+SIGN(d)+SIGN(e)+SIGN(f)

Assuming no negative values.

07 August 2009 17:13 by simonsabin

# re: TSQL Challenge - counting non null columns

Peso also emailed me with the SIGN solution

SIGN(a) + SIGN(b) + SIGN(c) + SIGN(d) + SIGN(e) + SIGN(f)

07 August 2009 17:14 by simonsabin

# re: TSQL Challenge - counting non null columns

Another opting for the SIGN solution

Thanks Gvee

07 August 2009 17:15 by simonsabin

# re: TSQL Challenge - counting non null columns

A flurry of emails using the SIGN option included fellow MVP Pawel

07 August 2009 17:16 by simonsabin

# re: TSQL Challenge - counting non null columns

Another email from Peso

If columns are varchar(1) or char(1) LEN(a+b+c+d+e+f) - LEN(REPLACE(a+b+c+d+e+f, '0', ''))

07 August 2009 17:18 by simonsabin

# re: TSQL Challenge - counting non null columns

I like Peso's solution thats a neat trick.

We are looking for non null values and so that you can shorten it to just LEN(REPLACE(a+b+c+d+e+f),'0','')