Merging records in a query on the fly -- a problem scenario

Could these records be merged so that the remaining columns no longer remain mutually exclusive.

Here is the problem scenario that brought up this question:

There is a master table listing some users of a system with assigned IDs called UserUIDs.

A user can have multiple roles to play in each of five different activity areas in the system.

There are a pair of tables for each of these activity areas -- a master table on the activity and a child table to connect the user master table with the respective activity master table.

I need to write an sp for generating a report on all the users of the system with the info in the following info --

user Info columns | activity 1 role | activity 2 role | more activity

| | |
| | |
| | |
| | |

The problem I face is this :

the roles that the user plays in each activity are independent of the others.

If user has 5 roles in activity 1, 3 roles in activity 2, and so on (5 being the maximum roles he has in a single activity) then the total number of records I am getting is the sum of the number of roles in all the activities (5+3+2+...).

I am getting this by doing a UNION of queries which fetch values for one particular activity and assign nulls to others.

Instead, i want the number of records to be the same as the maximum number of roles that a user has in any single activity (5). That is, the role columns should not be mutually exclusive.

(I am using Microsoft SQL Server 7.0 on Windows 2000)