Mhluzi Bhaka Mhluzi Bhaka - 9 months ago 42
SQL Question

Convert bitwise data into multiple columns

I have inherited user data that I need to sanitize. It originally was in an MS Access database and I need to convert it to SQL Server. I have the table in SQL Server now but the there is a column of data that I need help with.

There are a number of columns such as name, email etc which are all pretty straightforward.

However, there is one column in which bitwise data is stored. This column is to show the groups a user can belong to. By going through a number of records I have determined these groups and their associated number to be:

All 1
Air 2
Plants 4
Energy 8
Land 16
Elec 32
Fire 64
Water 128
Cloud 256
Soil 512
Waste 1024
Local 2048
Coast 4096

The data in the user column could be a 2 where they belong to the Air group, or it could be a 6 when they belong to both Air and Plants (2 + 4).

I have created a table that includes a user ID field.


So I need to iterate through the old table and then insert a corresponding true value in the columns of the new table accordingly

Thus, if the data is:

User_ID Name Group
1 Jo Smith 2
2 Carl White 6

I would end up with

User_ID All Air Plants Energy ....
1 true
2 true

and so on.

However, because users can belong to more than one group the data looks more like this:

Jake C 1552
Jeff H 1556
Cath B 1561
Emma B 1564
Alex G 1572
Alan H 1574
Jo L 1596
Roy A 1600

How do I go about building out my new table? I had thought something along the lines of

SELECT * FROM [dbo].[Users] where [Group] & 2 != 0

would start bringing back data along the lines that I need but I am not sure if that is the right track.

Edit: I am open to using external code if that is what is required - doesn't necessarily have to be done with SQL commands/queries

Answer Source
select      user_id

           ,sign ([Group] &  1   ) as [All]     
           ,sign ([Group] &  2   ) as [Air]     
           ,sign ([Group] &  4   ) as [Plants]  
           ,sign ([Group] &  8   ) as [Energy]  
           ,sign ([Group] &  16  ) as [Land]   
           ,sign ([Group] &  32  ) as [Elec]    
           ,sign ([Group] &  64  ) as [Fire]    
           ,sign ([Group] &  128 ) as [Water]   
           ,sign ([Group] &  256 ) as [Cloud]   
           ,sign ([Group] &  512 ) as [Soil]    
           ,sign ([Group] &  1024) as [Waste]   
           ,sign ([Group] &  2048) as [Local]   
           ,sign ([Group] &  4096) as [Coast]   

from        [dbo].[Users]


select      user_id

           ,sign ([Group] &  power(2, 0)) as [All]     
           ,sign ([Group] &  power(2, 1)) as [Air]     
           ,sign ([Group] &  power(2, 2)) as [Plants]  
           ,sign ([Group] &  power(2, 3)) as [Energy]  
           ,sign ([Group] &  power(2, 4)) as [Land]   
           ,sign ([Group] &  power(2, 5)) as [Elec]    
           ,sign ([Group] &  power(2, 6)) as [Fire]    
           ,sign ([Group] &  power(2, 7)) as [Water]   
           ,sign ([Group] &  power(2, 8)) as [Cloud]   
           ,sign ([Group] &  power(2, 9)) as [Soil]    
           ,sign ([Group] &  power(2,10)) as [Waste]   
           ,sign ([Group] &  power(2,11)) as [Local]   
           ,sign ([Group] &  power(2,12)) as [Coast]   

from        [dbo].[Users]