Filling in Blank Columns in a Table

[proballin]

I have a table with Employees and Employee Roles. Employees can have any number of roles. There are 27 roles to be exact. Now the thing is some employees may have only one role all the way on the 27th role column. What I want to do is update the table to where for each employee, all of their roles are copied to the first open column available. This way there arent many blanks between columns. Example

This is how it looks now:

Employee Role Role Role Role Role
1 x x
2 x x
3 x x x x
4 x
5 x

However I want this:

Employee Role Role Role Role Role
1 x x
2 x x
3 x x x x
4 x
5 x

 

[boblarson]

The way you currently have it set up is not good for a relational database. It is "spreadsheet thinking" instead of database thinking. You should have a table with a row for each combo of employee and their role.