In SQL, how can I split the values in a bitmask total into a comma delimited string

Question

I have a bitmask value stored as an int in sql. I'd like to turn that value into a comma separated list of the values contained in the bitmask value.

So, for example, the results might look like so:

id  name      bitMaskValue   values
----------------------------------------
1   Bob       5              1,4
2   Mary      13             1,4,8
3   Stan      11             1,2,8

Is there a way to accomplish this in a sql statement?

This is SQL Server 2008.

Answer 1

This should work:

SELECT id, name, bitMaskValue,
    SUBSTRING(
            CASE WHEN bitMaskValue & 1 = 1 THEN ',1' ELSE '' END
          + CASE WHEN bitMaskValue & 2 = 2 THEN ',2' ELSE '' END
          + CASE WHEN bitMaskValue & 4 = 4 THEN ',4' ELSE '' END
          + CASE WHEN bitMaskValue & 8 = 8 THEN ',8' ELSE '' END
        , 2, 64) As [values]
FROM yourTable

Answer 2

declare @I integer = 2117

Declare @v varChar(32) = ''
Declare @Bit tinyInt = 0
while @I > 0 Begin
Set @v += case @I %2 WHen 1 Then str(@bit,2,1) + ',' else '' End 
Set @Bit += 1
Set @i /= 2
End
Select case  When len(@v) > 0 Then left(@v, len(@v) -1) else '' End