Binary split

  • My database currently stores an enumeration value in the multiples of 2 where the max value stored would be 2^8 -1 .

    So consider for the value in the column i.e. 20, I have to add columns to a view saying that a particular value in the DB is a combination of 2^2 and 2^4 i.e. 4 + 16 = 20

    --This is Just a crude e.g. of what i am trying to achieve

    SELECT Enumeration,CASE Enumeration

    WHEN 4 THEN 1

    WHEN 20 THEN 1 ELSE 0 END AS '[2^2]', 0 AS '[2^3]',

    CASE Enumeration

    WHEN 20 THEN 1 ELSE 0 END AS '[2^4]'

    FROM

    (SELECT 4 AS Enumeration

    UNION ALL

    SELECT 20 AS Enumeration) E

    So basically I need to split the Enumeration Field in to its Binary equivalent and split each of those binary values into individual columns, so that

    I can use it as an attribute ( bit flag) to depict that it is a combination of the mentioned values.

    I reached till the binary part of it, however was stuck into splitting the binary values individually into seperate columns.

    Also the function I am trying to use is based on a while loop,

    which i am sure would not be very efficient if i am going to call it for each of the values of the Enumeration.

    Any ideas/approach appreciated.

  • I think that this is what you needed:

    create table #t( Num int)

    insert into #t (num)

    values (20)

    insert into #t (num)

    values (4)

    ;with cte (StartPoint,TheRest,ToContinue,Col )

    as

    (

    select Num, Num % 2, Num/2 ,1 from #T

    union all

    select Num,ToContinue%2,ToContinue/2,COL+1 from cte

    inner join #t on #t.Num=cte.StartPoint

    --where cte.ToContinue>0where cte.Col<=7

    )

    Select StartPoint,SUM(col1) as [2^7],SUM(col2) as [2^6],sum(col3) as [2^5] ,SUM(col4) as [2^4],SUM(col5)as [2^3],SUM(col6) as [2^2],SUM(col7) as [2^1],SUM(col8)as [2^0] from

    (

    select StartPoint, case [col] when 8 then TheRest else 0 end as Col1,

    case [col] when 7 then TheRest else 0 end Col2 ,

    case [col] when 6 then TheRest else 0 end Col3 ,

    case [col] when 5 then TheRest else 0 end Col4 ,

    case [col] when 4 then TheRest else 0 end Col5 ,

    case [col] when 3 then TheRest else 0 end Col6 ,

    case [col] when 2 then TheRest else 0 end Col7 ,

    case [col] when 1 then TheRest else 0 end Col8 from cte --order by StartPoint,Col

    ) as X

    group by StartPoint

    drop table #t

  • What I am seeing here is a bitwise implementation. Here is a very good article on bitwise that may help.

    http://www.databasejournal.com/features/mssql/article.php/3359321/Storing-Multiple-Statuses-Using-an-Integer-Column.htm

  • If you're forced to work with this denormalised method of storage, then you split the bit flags out into separate columns using the following query that should be faster than any method that uses a RECURSIVE CTE or WHILE loop. Obviously, it relies on the maximum number of bit columns being known, but this seems to be the case with your requirements.

    SELECT

    N,

    SIGN(N & 1) AS Bit1,

    SIGN(N & 2) AS Bit2,

    SIGN(N & 4) AS Bit3,

    SIGN(N & 8) AS Bit4,

    SIGN(N & 16) AS Bit5,

    SIGN(N & 32) AS Bit6,

    SIGN(N & 64) AS Bit7,

    SIGN(N & 128) AS Bit8

    FROM (

    SELECT 4 UNION ALL

    SELECT 20 UNION ALL

    SELECT 63 UNION ALL

    SELECT 100 UNION ALL

    SELECT 255

    ) TestData(N)

  • Great work! Thanks for the inputs . The article was very helpful to understand the implementation, however reverse engineering it was a bit difficult.

    @andrew's solution worked like a charm for this.Thanks!

  • How would i get to Identify which columns have been got set to 1, ie. If I had to represent the column names with a comma seperated list ?The one in the

    article uses a where clause and | (OR) functionality just returns me the list of the Enumeration which are a part of either of the clauses. with using the & ,| combo

    How could i be able to depict it in a column like Bit2,Bit3 and so on ?

  • Here are 2 ways:

    SELECT

    N,

    STUFF(

    CASE WHEN (N & 1) > 0 THEN ',Bit1' ELSE '' END

    + CASE WHEN (N & 2) > 0 THEN ',Bit2' ELSE '' END

    + CASE WHEN (N & 4) > 0 THEN ',Bit3' ELSE '' END

    + CASE WHEN (N & 8) > 0 THEN ',Bit4' ELSE '' END

    + CASE WHEN (N & 16) > 0 THEN ',Bit5' ELSE '' END

    + CASE WHEN (N & 32) > 0 THEN ',Bit6' ELSE '' END

    + CASE WHEN (N & 64) > 0 THEN ',Bit7' ELSE '' END

    + CASE WHEN (N & 128) > 0 THEN ',Bit8' ELSE '' END

    , 1, 1, '') AS BitList

    FROM (

    SELECT 4 UNION ALL

    SELECT 20 UNION ALL

    SELECT 63 UNION ALL

    SELECT 100 UNION ALL

    SELECT 255

    ) TestData(N)

    The second method uses the FOR XML PATH('') method to concatenate

    ;WITH cteBits (Tag, BitValue) AS (

    SELECT 'Bit1', 1 UNION ALL

    SELECT 'Bit2', 2 UNION ALL

    SELECT 'Bit3', 4 UNION ALL

    SELECT 'Bit4', 8 UNION ALL

    SELECT 'Bit5', 16 UNION ALL

    SELECT 'Bit6', 32 UNION ALL

    SELECT 'Bit7', 64 UNION ALL

    SELECT 'Bit8', 128

    )

    SELECT TestData.N,

    STUFF((

    SELECT ',' + B.Tag FROM cteBits B

    WHERE (TestData.N & B.BitValue) > 0

    ORDER BY B.BitValue

    FOR XML PATH('')

    ), 1, 1, '') AS BitList

    FROM (

    SELECT 4 UNION ALL

    SELECT 20 UNION ALL

    SELECT 63 UNION ALL

    SELECT 100 UNION ALL

    SELECT 255

    ) AS TestData(N)

  • Darn! I knew it wasn't that complicated, I have used XML 'n' number of times before as well to concatenate the columns. If only I would have stressed my self more on how we arrived at the bit flags to set it 1, would have solved the mystery.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply