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

From: stu <smcgouga_at_nospam.co.uk>
Date: Wed, 26 Feb 2003 16:18:58 -0000
Message-ID: <b3ipgg$bu7$1$8302bc10_at_news.demon.co.uk>


Does the distinct keyword help?

Cheers
Stu

"Andy" <garth_rockett_at_yahoo.com> wrote in message news:96dbbc80.0302250936.68ab573_at_posting.google.com...
> 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)
>
> Cheers,
> Andy
Received on Wed Feb 26 2003 - 17:18:58 CET

Original text of this message