--**************************************
-- Name: Decode powers of 2
-- Description:decode powers of 2 as columnar values or rows.
-- By: Umachandar
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=163&lngWId=5--for details.--**************************************
declare @n int
select @n = 21
-- Decode as columns...
select
case
when power(2, 0) & @n = power(2, 0) then power(2, 0)
else null end as Val1,
case
when power(2, 1) & @n = power(2, 1) then power(2, 1)
else null end as Val2,
case
when power(2, 2) & @n = power(2, 2) then power(2, 2)
else null end as Val3,
case
when power(2, 3) & @n = power(2, 3) then power(2, 3)
else null end as Val4,
case
when power(2, 4) & @n = power(2, 4) then power(2, 4)
else null end as Val5
-- Decode as rows...
SELECT p2 AS Val
FROM (SELECT power(2, 0) As p2 UNION ALL
SELECT power(2, 1) As p2 UNION ALL
SELECT power(2, 2) As p2 UNION ALL
SELECT power(2, 3) As p2 UNION ALL
SELECT power(2, 4)As p2) AS p2
JOIN (SELECT @n AS Num) AS n
ON NULLIF(p2 & @n, p2) IS NULL
|
