Re: Moving Multiple Rows to a Single Row

From: J.P. <jp_boileau_at_yahoo.com>
Date: 4 Apr 2002 11:55:13 -0800
Message-ID: <7e388bc3.0204041155.7b18440d@posting.google.com>

Bob,

There's a way to do this. However, I don't know what it is. - Just kidding. I do know.

Here's what you can do. It's not particularly pretty but it works.

select a.id, b.id, c.id
from

   (select distinct id from table1) a,
   (select id from table1 where lval is not null) b,
   (select id from table1 where cval is not null) c

where

   b.id(+) = a.id and
   c.id(+) = a.id

From the sample data you provided, I get this when I run the above query:

SQL> select a.id, b.id, c.id
  2 from

  3     (select distinct id from table1) a,
  4     (select id from table1 where lval is not null) b,
  5     (select id from table1 where cval is not null) c
  6  where
  7     b.id(+) = a.id and
  8     c.id(+) = a.id

  9
SQL> /         ID ID ID
---------- ---------- ----------

         1          1          1
         2          2          2
         3          3
         4                     4
         5          5          5
         6
         7          7          7

7 rows selected.

SQL> I think that's what you want.

JP

Time Vest Insurance <reb_at_timevest.com> wrote in message news:<l8zd6xgi7ch.fsf_at_timevest.com>...
> Hi,
>
> I'm trying to solve a particular type of problem that may be related
> to the Pivot_Table methodology mentioned this week in the
> newsgroup. However, it is enough different that I can't see how to
> apply it.
>
> Essentially, I have a long convoluted query that produces results like
> this:
>
>
> ID LVAL CVAL
> -------- -------- -------
> 1 A NULL
> 1 NULL Q
> 2 B NULL
> 2 NULL R
> 3 C NULL
> 4 NULL S
> 5 D NULL
> 5 NULL T
> 6 NULL NULL
> 7 E NULL
> 7 NULL U
>
>
> I want the results to be:
>
>
> ID LVAL CVAL
> -------- -------- -------
> 1 A Q
> 2 B R
> 3 C NULL
> 4 NULL S
> 5 D T
> 6 NULL NULL
> 7 E U
>
>
> In fact, I really want to assign a numeric value and have the results
> be more like:
>
>
> ID LVAL CVAL
> -------- -------- -------
> 1 1 1
> 2 2 2
> 3 3 NULL
> 4 NULL 3
> 5 4 4
> 6 NULL NULL
> 7 5 5
>
>
> DECODE would be really cool for this but I can't use it because the
> query has to work in Sybase and SQL Server as well as Oracle.
>
> Anyone have some brilliant ideas?
>
> Are there any books on Advanced SQL Techniques?
>
>
> Bob
Received on Thu Apr 04 2002 - 13:55:13 CST