Here is my situation: I have a table of users in my database with all the usual user information it it. I have another table which lists the rights that each user has. this table has 2 columns 'Userid','Functionid'. Each user may have multiple rows in this table, for example if an ID of a user in the main users table was 10, they may have some rows like this in the 2 column table:
USERID FUNCTIONID 10 3 10 5 10 6
I need to find a select statement that can combine these rows into a single row which also includes the information from the users row in the user table
USERID FIRSTNAME LASTNAME FUNCTION1 FUNCTION2 FUNCTION3 10 Some User 3 5 6
Is it possible to do this? The number of function thats any given user will have is not set either, so they could have 1 or have 10 or more.