SQL Selecting multiple rows based on binary to decimal conversion

Question

Sorry the title is a little vague.

I have inherited a table like this...

HG_ID | HG_Description
1     | Blue
2     | Red
4     | Green
8     | Yellow
16    | White
32    | Violet
64    | Black

The information is based on a 7 bit binary eg 0000001 represents Blue 0000011 Represents Blue + Red

I have another table that contains the decimal equivalent.

0000001 = 1 in the second table
0000011 = 3 in the second table.

My issue is that if I have 66 in the second table I don't know how to do a select statement that gives me Black and Red in the same cell.

I know how to do it on the numbers that appear the same in each table but not when there is more than one selection.

I hope I have made that clear it is my first post.

Thank you. Mal

Answer 1

You can get the appropriate rows from this table using the bitwise-and operator &. For example (SQL Fiddle):

DECLARE @colorValue int = 66

SELECT * 
FROM Table1
WHERE HG_ID & @colorValue = HG_ID

You can then use the FOR XML PATH('') trick to simulate MySQL's GROUP_CONCAT() function and get this down to one row (SQL Fiddle):

DECLARE @colorValue int = 66

SELECT STUFF(
    (SELECT ', ' + CAST(HG_Description AS VARCHAR(MAX)) 
     FROM Table1 
     WHERE HG_ID & @colorValue = HG_ID
     FOR XML PATH('')
    ), 1, 2, '')

---

You can of course use this to select from another table. Assume you have a table Table2 with the following:

CREATE TABLE Table2
    ([COLOR] int);

INSERT INTO Table2
    ([COLOR])
VALUES
    (66),
    (23),
    (49)
;

The query, extended from above,

SELECT Table2.COLOR AS id,
    STUFF(
    (SELECT ', ' + CAST(HG_Description AS VARCHAR(MAX)) 
     FROM Table1 
     WHERE HG_ID & Table2.COLOR = HG_ID
     FOR XML PATH('')
    ), 1, 2, '') AS Colors
FROM Table2

Will produce the following results (SQL Fiddle):

ID  COLORS
66  Red, Black
23  Blue, Red, Green, White
49  Blue, White, Violet