Sql Server 2008 With Array

Question

I have two tables in my sql:

Users :

id    name     roleid
1     David    1
2     Sean     2
3     Joe      1

Roles:

roleid    desc
1         copy
2         delete
3         move

Now i use this cmd to select the user with the user permission

SELECT * FROM Users u INNER JOIN Roles r ON u.roleid = r.roleid

Now i want to know if it's possible to build SQL Table(Roles Table), that it's will be dynamically the number of roleid for each user. something like:

Users :

id    name     roleid roleid2 roleid3
1     David    1      2       3
2     Sean     2   
3     Joe      1      3

How about using a View? (msdn.microsoft.com/en-us/library/aa214068%28v=sql.80%29.aspx) — cheesemacfly
– cheesemacfly, Commented Feb 4, 2013 at 21:11
It can be used as a virtual table yes and it states in the documentation: "Join columns from multiple tables so that they look like a single table." That seems to be what you are looking for. — cheesemacfly
– cheesemacfly, Commented Feb 4, 2013 at 21:16
But i don't understand how i can use it to build a table like users with dynamically roleid columns — YosiFZ
– YosiFZ, Commented Feb 4, 2013 at 21:18
I don't see where this data currently lives. Is each user listed more than once in the users table if they have more than one role, or is there another table (UserRoles) that you didn't share? — Joel Coehoorn
– Joel Coehoorn, Commented Feb 4, 2013 at 22:10

Bryan · Accepted Answer · 2013-02-04 22:03:20Z

1

Use an associative entity to address the many-to-many relationship between Users and Roles. A composite primary key in the UserRole table will prevent duplicate assignment of roles, and foreign keys referencing the Users and Roles table will preserve referential integrity.

See SQL fiddle for a sample implementation.

answered Feb 4, 2013 at 22:03

Bryan

17.7k7 gold badges59 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dima Railguner · Accepted Answer · 2013-02-04 22:06:12Z

0

At first, I suggest you to use many to many relationship. It means trird table: UsersRoles (userid,roleid) At second, it's impossible create dynamic number of columns in typical SQL statement. But its possible by using stored procedures.

here is working example sqlfiddle

edited Feb 4, 2013 at 22:06

answered Feb 4, 2013 at 21:26

Dima Railguner

1735 silver badges15 bronze badges

Collectives™ on Stack Overflow

Sql Server 2008 With Array

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related