How can I do SQL JOIN by bitwise operator manipulation?

user_7189374 0
Viewed 829

We have a SQL Server 2014 database containing two tables:

enter image description here

enter image description here

In the Parts table BitColours is a bitwise number referencing rows from the Colours table.

we have been asked to produce a query like this:

enter image description here

I've never done this type of manipulation before so would appreciate any guidance.

Thanks

sql-server-2014 bit-manipulation tsql sql-server sql
1 Answers

You can try to connected two tables by AND bitwise operator(&).

Query 1:

SELECT
    p.id,
    p.part,
     STUFF((
          SELECT ',' + c.Colour
          FROM colours c
          WHERE c.BitId & p.BitColurs > 0
          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') colours
FROM parts p

Results:

| id |   part |     colours |
|----|--------|-------------|
|  1 | abc123 |    Red,Blue |
|  2 | xyt788 | Black,Green |

Explain

& AND bitwise operator will compare two binary from two value.

for example :

BitColor is 3 = 00011.

here is a sample I ignore many zero after binary 16.

Red

    00011
---------------------
(&) 00001 = Red (1)
---------------------
    00001 = (1)

Blue

    00011
---------------------
(&) 00010 = Blue (2)
---------------------
    00010 = (2)

Black

    00011
---------------------
(&) 00100 = Black (4)
---------------------
    00000 = (0)

Green

    00011
---------------------
(&) 01000 = Green (8)
---------------------
    00000 = (0)

So we can use BitId & BitColurs do calculation by binary, and get the result which greater 0 mean You can get the value.

user_51760710
Related