Many-to-one merge of records in one table using a SELECT?

Many-to-one merge of records in one table using a SELECT?

Post by Ano » Sun, 17 Nov 2002 00:22:52



Greetings, one and all.

I have a question regarding the possibility of using SQL (in some
form) for the following task:

I have a table which stores scientific data about light emitted from
objects measured in different filters:  

Set#  Object#  Filter#  ....[Other columns]...
----  -------  -------  ----------------------

The Set# and Object# columns uniquely identify any one object.  The
number of entries in the table for any given (Set#, Object#) is equal
to the number of filters it was measured in.  So three filters in a
set == three records in the table for that object (for that set).

So, the general idea of what I'm trying to do is SELECT all of the
fields for any given object as 1 record / object, as opposed to n
records / object where n=number of filters.  The number of filters
could be different from set to set.

This seems like this should be a basic, common task in SQL but I
haven't had any success using views/joins to output the data in the
format I want.

Has anybody here done this kind of many-to-one transformation before
using SQL?

Any ideas/suggestions would be greatly appreciated.  Thanks.

Anon

 
 
 

Many-to-one merge of records in one table using a SELECT?

Post by Anthony Mandi » Sun, 17 Nov 2002 11:03:06



> So, the general idea of what I'm trying to do is SELECT all of the
> fields for any given object as 1 record / object, as opposed to n
> records / object where n=number of filters.  The number of filters
> could be different from set to set.

> This seems like this should be a basic, common task in SQL but I
> haven't had any success using views/joins to output the data in the
> format I want.

> Has anybody here done this kind of many-to-one transformation before
> using SQL?

        It sounds like you want to do table folding and pivoting. Its
        doable but not very easy when you have a variable number of
        rows. The basic technique is described in the book -

        Optimizing Transact-SQL: Advanced Programming Techniques
        by Rozenshtein, Abramovich and Birger. SQL Forum Press.
        1995. ISBN 0-9649812-0-3.

        You'd probably want to replace some of the arcane code with
        case statements too.

-am     ? 2002