Tutorial :TSQL. Get Bitwise total for many columns in a row



Question:

I have a table that has 4 sets of 25 columns in a BIT concept. Actually field is smallint but it is either 0 or 1 in it's data.

Here is my code that is an attempt to get the total for the first group of 25 cols.

Declare @rows int  , @ID uniqueidentifier  , @LocTotal bigint      select @rows =  ( select  count(*) from #t1 )    while @rows > 0  begin  print @rows  -- get that rowID         select @ID = (select top 1 recid from #t1)  select @LocTotal =  (select top 1  case when cbHPILoc1 = 1 then 1 else 0 end +  case when cbHPILoc2 =  1 then 2 else 0 end +  case when cbHPILoc3 = 1 then  4 else 0 end +  < snip >  case when cbHPILoc25 = 1 then 16777216 else 0 end  as Total   from  dbo.MyTest_BitMap  where RecKey = @ID  )         print @ID  print  @LocTotal  

My output:

(5 row(s) affected)  5  67A16306-B27D-4882-88A2-1146CBAAA8D9    (1 row(s) affected)  4  F94929B9-3DA7-4AA3-96F6-728EF025B21C  

I fail to get the Total to @LocTotal

TIA


Solution:1

I think Tomalak has it, which is why I modded him up, but if you do this more or once, since all that mess:

cbHPILoc1    + cbHPILoc2 * 2    + cbHPILoc3 * 4    + ...    + cbHPILoc25 * 16777216  

is tedious and error prone, why not do it once as a view and test that thoroughly?

Then just select total from viewname where reckey = ?.


Solution:2

Why so complicated?

SELECT    RecKey,    cbHPILoc1    + cbHPILoc2 * 2    + cbHPILoc3 * 4    + ...    + cbHPILoc25 * 16777216    AS Total  FROM    dbo.MyTest_BitMap  WHERE     RecKey = @ID  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »