Re: A SQL question, please help...

Of course you can do it with a single SQL statement (when using an Oracle database) but it isn't going to be pretty.

select a0.id id, a0.the_date, a1.a1, a2.a2, a3.a3 from (select trunc(date) the_date, min(id) id

   from table a
   group by trunc(date)
) a0,
(select a1a.the_date, a.a1 from a,
 (select trunc(date) the_date, min(rowid) a1_rowid

    from table a
    where a1 is not null
    group by trunc(date)
 )a1a where a1a.a1_rowid = a.rowid
) a1,
(select a2a.the_date, a.a2 from a,
 (select trunc(date) the_date, min(rowid) a2_rowid

    from table a
    where a2 is not null
    group by trunc(date)
 )a2a where a2a.a2_rowid = a.rowid
) a2,
(select a3a.the_date, a.a3 from a,
 (select trunc(date) the_date, min(rowid) a3_rowid

    from table a
    where a3 is not null
    group by trunc(date)
 )a3a where a3a.a3_rowid = a.rowid
) a3

  where a0.the_date = a1.the_date (+)
    and a0.the_date = a1.the_date (+)
    and a0.the_date = a1.the_date (+)

Also, this isn't going to be effecient (not in the slightest). A much better recommendation would be to fix the system that originally creates the data so that it updates an existing row if it exists with the a1, a2 or a3 values.

Jimmy wrote:

> Hello all,
>
> I have the following rows in table A:
>
> ID date A1 A2 A3
> 1 01-Jan-98 10
> 2 01-Jan-98 10 11
> 3 01-Jan-98 12
> 4 01-Feb-98 20 21
> 5 01-Feb-98 20 22
> 6 01-Feb-98
>
> Field value is the same with the same date. The table, in fact, has
> problem of splitting the same date row into different rows. So I want to
> combine the same date row as the following table:
>
> ID date A1 A2 A3
> 1 01-Jan-98 10 11 12
> 4 01-Feb-98 20 21 22
>
> i.e. combine the rows with the same date, use the smallest ID among the
> same date rows. The field value may be character.
>
> Can I do that by using SQL statement?
>
> Thanks,
> Jimmy
Received on Tue Nov 24 1998 - 00:00:00 CET