combine multiple rows/ columns into single row SQL ASP.NET

Question

I have a user table, a role table that holds roleID and name, and a userrole table that has roleID and userID. Users can have multiple roles, so there could be multiple records returned for my select query. Is there a way that I can combine each roleID into a single row with a new column added for the additional roleID if it exists, either through a sql proc or in VB? I have a gridview that I will use for role management, it has 3 columns for roles, each with a checkbox that allows adding or deleting a role.

You should separate display logic from database schema. retrieve your data "as is" then recreate new data source object (eg. new DataTable) — aifarfa
– aifarfa, Commented Dec 13, 2013 at 3:55

sh1rts · Accepted Answer · 2013-12-13 03:33:22Z

4

Don't store multiple items in a single database column; you're violating first normal form, which is not good database design.

In your case, you could create a View that aggregates your multiple roles into a single "column" (comma delimited), and then base your gridview's datasource on the new View, instead of the base table.

You could do the same thing through a stored procedure.

answered Dec 13, 2013 at 3:33

sh1rts

1,8841 gold badge13 silver badges14 bronze badges

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

2 Comments

Bob Over a year ago

I actually did not want to delimit them into a single column, rather add an additional column onto the row only if the user has multiple roles. I do see your point and I want to keep it normalized.

sh1rts Over a year ago

A view or a stored proc would still probably be your solution Bob

Collectives™ on Stack Overflow

combine multiple rows/ columns into single row SQL ASP.NET

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related