May 2, 2011 at 2:44 pm
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.
May 2, 2011 at 10:02 pm
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
May 2, 2011 at 10:29 pm
What I am seeing here is a bitwise implementation. Here is a very good article on bitwise that may help.
May 3, 2011 at 5:14 am
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)
May 3, 2011 at 3:41 pm
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 ?
May 4, 2011 at 3:50 am
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)
May 4, 2011 at 7:19 am
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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply