CODEWITHSUNDEEP
sqlsql-serverdatabasebitwise-operators
Mhluzi Bhaka
Mhluzi Bhaka

Reputation: 1392

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.

User_ID
All 
Air
Plants
Energy
Land
Elec
Fire
Water
Cloud
Soil
Waste
Local
Coast

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     8

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

Upvotes: 1

Views: 182

Answers (1)

David דודו Markovitz
David דודו Markovitz

Reputation: 44921

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]

or

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]

select      u.*
           ,[All] + [Air] + [Plants] + [Energy] + [Land] + [Elec] + [Fire] + [Water] + [Cloud] + [Soil] + [Waste] + [Local] + [Coast]       as cnt

from       (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]
            ) u
;

Upvotes: 3

Related Questions