CODEWITHSUNDEEP
sql-serverbinary
ZedZip
ZedZip

Reputation: 6470

T-SQL: How to add bits in binary fields?

I have a table

create table t (id int, b binary(128))

insert into t(id, b) values (1, 0x010000)
insert into t(id, b) values (1, 0x000204)
insert into t(id, b) values (2, 0x000004)
insert into t(id, b) values (2, 0x010204)
insert into t(id, b) values (2, 0x010004)

I need to receive finally summary table contains 2 records where b is a OR'ed bits

create table result (id int, b binary(128)) 

1 0x010204
2 0x010004

It seems I need to do this

select id, FUN(b) from t group by id

The question is: I need to create and use SQLCLR aggregate function FUN. Is there another way?

Upvotes: 0

Views: 637

Answers (1)

Solomon Rutzky
Solomon Rutzky

Reputation: 48836

SQLCLR is not required, though it would be more readable ;-). I was able to do this via the following which required a cursor but I couldn't find a way around it (APPLY would be nice but doesn't quite work in this context).

/* given sample data but as temp table and BINARY(5) instead of BINARY(128) */
CREATE TABLE #T (ID INT, B BINARY(5));  --DROP TABLE #t
INSERT INTO #t VALUES (1, 0x010000); INSERT INTO #t VALUES (1, 0x000204);
INSERT INTO #t VALUES (2, 0x000004); INSERT INTO #t VALUES (2, 0x010204);
INSERT INTO #t VALUES (2, 0x010004);
SELECT *, CONVERT(BIGINT, b) AS [converted] from #t
------------------------
DECLARE @Totals TABLE (ID INT, B BIGINT);
DECLARE @ID INT, @B BIGINT;

DECLARE curs CURSOR FAST_FORWARD LOCAL
FOR  SELECT src.id, CONVERT(BIGINT, src.b) FROM #t src;

OPEN curs;
FETCH NEXT FROM curs INTO @ID, @B;

WHILE (@@FETCH_STATUS = 0)
BEGIN
    UPDATE ttl
    SET    ttl.B |= @B
    OUTPUT INSERTED.*, @B AS [CurrentB] -- debug
    FROM   @Totals ttl WHERE  ttl.ID = @ID;

    IF (@@ROWCOUNT = 0)
    BEGIN
        INSERT INTO @Totals (ID, B) VALUES (@ID, @B);
    END;

    FETCH NEXT FROM curs INTO @ID, @B;
END;

CLOSE curs; DEALLOCATE curs;

SELECT 'Result' AS [~~], ttl.ID, ttl.B, CONVERT(BINARY(5), ttl.B) AS [FinalB]
FROM @Totals ttl;

Of course, if you truly need a BINARY(128), then this might not work.

Upvotes: 2

Related Questions