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

From: Andy <>
Date: 25 Feb 2003 09:36:15 -0800
Message-ID: <>

I was wondering if there are any specific tools for merging records with one set of columns having identical values and other set of columns having mutually exclusive values i.e. of say 10 cols, 7 have the same value across three records.
Of the remaining three cols, only one is non-null in any record.

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)

Andy Received on Tue Feb 25 2003 - 18:36:15 CET

Original text of this message