Decoding a Numeric Field - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Decoding a Numeric Field

Author

Topic

singularity
Posting Yak Master

153 Posts

Posted - 2008-07-17 : 19:50:48

I've got two tables that looks like this:

Role_Cd Role
-------------
1 A
2 B
4 C
8 D
16 E
32 F
64 G
etc.

User_ID Role_Cd
-----------------
1 4
2 12
3 49

The rule is that in the second table the Role_Cd will be a sum of the user's roles from the first table. A user could have an unlimited number of roles. My goal is to break down that number and show a row for each role, like so:

User_ID Role
------------
1 C
2 C
2 D
3 A
3 E
3 F

Is this possible?

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-07-17 : 23:32:26

Assuming that a user can only have a role one time then yes it is possible.
Look at case

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-18 : 02:59:55

[code]DECLARE @role TABLE
(
role_cd int,
role CHAR(1)
)
INSERT INTO @role
SELECT 1, 'A' UNION ALL
SELECT 2, 'B' UNION ALL
SELECT 4, 'C' UNION ALL
SELECT 8, 'D' UNION ALL
SELECT 16, 'E' UNION ALL
SELECT 32, 'F' UNION ALL
SELECT 64, 'G'

DECLARE @user TABLE
(
user_id int,
role_cd int
)

INSERT INTO @user
SELECT 1, 4 UNION ALL
SELECT 2, 12 UNION ALL
SELECT 3, 49

SELECT u.user_id, r.role
FROM @user u
INNER JOIN @role r ON r.role_cd & u.role_cd <> 0
ORDER BY u.user_id, r.role

/*
user_id role
----------- ----
1 C
2 C
2 D
3 A
3 E
3 F

(6 row(s) affected)
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

singularity
Posting Yak Master

153 Posts

Posted - 2008-07-18 : 09:02:48

KH, that was awesome! Thanks a lot.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources