Yup, I'm sure there is a better way to get where I'm going... all and any advice appreciated ...

I have a table (tblBitMask) with two fields. A primary key and a non-null integer value field (BinaryValue).

The value field will always be 2 to the power of an integer (e.g. 1,2,4,8,16 etc.). This table is basically a lookup on a bitmask binary field in another table -- what's important is that not all the powers of 2 will be in there .. for example this table may contain:

1,2,8 (note, no 4)

I need to produce a table containing all the possible combinations of the numbers in tblVarBin. In this case that would be 8 results:

0,1,2,3,8,9,10,11

Final restriction .. must work in SQL Server 7.0

Now that I've found a very convoluted solution to this problem, it is probably time for some kind person to step forward and introduce me to a built in function that does this :).

Anyway, the way I've done it is below, and it should be apparant why I want to prepopulate the results table with 2 ^ (Row count in tblVarBin).

However, it still depends on there being a table with lots of rows. Maybe I could depend on sysobjects CROSS joined with itself, but I'ld rather not if I don't have to.

--------------------------------------

CREATE PROCEDURE up_BinaryCombinations

AS

DECLARE @intBlankRows int

SET @intBlankRows=Power(2,(SELECT Count(*) FROM tblBitMask))

--SELECT @intBlankRows

-- 1) Create a temp table (#tblResults) with a counter and a sum field with -- Count(*) FROM tblBitMask ^ 2 -- rows

CREATE TABLE #tblResults (

ResultsID int identity NOT NULL ,

Total int NOT NULL

)

-- **************************** HERE -- I DON'T WANT TO USE sysobjects .. cross join??? *******

SET ROWCOUNT @intBlankRows

INSERT INTO #tblResults (Total) SELECT 0 FROM sysobjects

SET ROWCOUNT 0

-- 2) Create a temp table (#tblBinaryValues) with a counter and the original Mask values (ordered asc)

CREATE TABLE #tblBinaryValues (

BinaryValueID int identity NOT NULL ,

BinaryValue int NOT NULL

)

INSERT INTO #tblBinaryValues (BinaryValue) SELECT BinaryValue FROM tblBitMask ORDER BY BinaryValue ASC

--3) Update @tblResults to show all possible combinations of binary values, based on the masks available in @tblBinaryValues

UPDATE tr

SET tr.Total=BitMaskSum

FROM

#tblResults tr,

(

SELECT

r.ResultsID,

Sum(bv.BinaryValue) As BitMaskSum

FROM

#tblResults r,

#tblBinaryValues bv

WHERE ((r.ResultsID-1) & Power(2,(bv.BinaryValueID-1)))=Power(2,(bv.BinaryValueID-1))

GROUP BY r.ResultsID

) AS tblDerived

WHERE tr.ResultsID=tblDerived.ResultsID

SELECT * FROM #tblResults

GO

which contains

0-n integers.