Re: possible/impossible query?

Take the problem apart and it becomes pretty simple

Step 1: INSERT INTO ...

            SELECT DISTINCT col1, col2, col3
            FROM ....
Step 2. Write a simple cursor to gather all values from all columns
            concatenating those that are unique. The update the records
            created in step 1.

Undoubtedly there is some complex way to do this in some massive single SQL statement with lots of decodes but I find them, while fun for personal challneges ... far to difficult to use in production apps with multiple developers, testing, and long-term maintenance requirements.

Dan Morgan

dxcreepin wrote:

> I am looking for guidance on a problem that has perplexed me for the
> past couple of months. Here is a description of what I have and what I
> am trying to do:
>
> Table: INFO (all the columns are varchar(1) – in this example)
> Col1 col2 col3 col4 col5 col6 col7
> a b c d d e f
> a b c g h
> a b c i
> 1 2 3 4 5 6 7
> 1 2 3 a b c
> 1 2 3 d e
> z y x w v u t
>
> The first 3 columns are not null and the remaining columns can be
> null. I am trying to develop a query and/or procedure/function/package
> to return this type result:
>
> Col1 col2 col3 col4 col5 col6 col7
> a b c d dgi eh f
> 1 2 3 4ad 5 6b 7ce
> z y x w v u t
>
> If there are duplicates of the first 3 columns, the remaining rows
> with not null data will be concatenated together into one field.
> Eventually I would like to use this in Oracle Reports. If you are
> familiar with PVCS Dimensions, I am trying to emulate the behavior of
> thee unlimited text fields.
>
> Can you please help me try to find a solution to this?
Received on Fri Apr 19 2002 - 20:54:00 CEST