In SQL, how can I split the values ​​in the resulting mass into a comma delimited string

Question

I have a bitmask saved as an int in sql. I would like to turn this value into a comma-separated list of values ​​contained in the bitmask value.

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

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

 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 

Answer 2

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 3

CTE + XPATH way :

 set nocount on declare @t as table(id int, name varchar(100), bitMaskValue int) insert into @t(id, name, bitMaskValue) values(1,'Bob',5) insert into @t(id, name, bitMaskValue) values(2,'Mary',13) insert into @t(id, name, bitMaskValue) values(3,'Stan',11) ;with cte(num) as ( select 1 union all select num*2 from cte ) select id, name, bitMaskValue, stuff(( select cast(( select isBitSet from ( select top 31 num, case when bitMaskValue & num = num then ',' + cast(num as varchar(10)) else null end isBitSet from cte ) tmp where isBitSet is not null for xml path('')) as xml).value('.', 'VARCHAR(max)') ), 1, 1, '') bitSet from @t