Re: Merge lines to get rid of nullvalues?

"andrewst" <member14183_at_dbforums.com> wrote
> Originally posted by Tim Gahnstroem
> > Is it possible to merge rows in SQL to get rid of null values?
> > I have a select query that gives me a table (simplified) on this form:
> >
> > A B C
> > Key1 "text1" null
> > Key1 null 7
> > Key2 "text2" null
> > Key2 null 3
> > Key3 null 9
> > Key4 "text4" null
> > Key4 null 79
> >
> > A is actually a number of columns but the values on the whole row only
> > differs in the last 2 columns (B and C).
> >
> > B is a CHAR column and C is a NUM
> >
> > I want to merge the rows with the same key and put the values in the
> > null-fields, like this:
> >
> > A B C
> > Key1 "text1" 7
> > Key2 "text2" 3
> > Key3 null 9
> > Key4 "text4"79
> >
> > As you can see, it is not always 2 keys, so some fields might still be
> > null.
> >
> > I might get around the problem by making several queries and
> > subqueries and join them afterwards but it will be very ugly and slow
> > so I would really like to know if there is another solution
> >
> > I don't think it is needed but anyhow, this is about how the SQL code
> > looks like, I interface Oracle with Excel VBA.
> >
> > SELECT A, B, C
> > FROM r5events EV, r5propertyvalues VAL, R5PROPERTIES PROP
> > and EV.evt_mrc= 'JKLO'
> > and VAL.prv_property=PROP.pro_code
> > and EV.evt_code=val.prv_code(+)

> Try:
>
> SELECT A, max(B), max(C)
> FROM r5events EV, r5propertyvalues VAL, R5PROPERTIES PROP
> and EV.evt_mrc= 'JKLO'
> and VAL.prv_property=PROP.pro_code
> and EV.evt_code=val.prv_code(+)
> group by a;

Aha! Ofcourse, that was more or less obvious, thanks a whole lot!

Tim Received on Mon Jul 28 2003 - 18:16:55 CDT