Re: Merge rows ro remove nullvalues?

"Tim Gahnstroem" <tim.gahnstroem_at_cern.ch> wrote in message news:c2fd55f1.0307280522.1dfd693a_at_posting.google.com...
> Hi
> 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 5
>
> 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" 5
>

---snip--

Assuming the sample data you show is a good representation of the actual data.

Consider table tableA has this kind of data.

Then create tableB like this:

create table tableB as select A, max(B), max(C) from tableA group by A;

then drop tableA ... and rename tableB as tableA and create the indexes/triggers etc. that go along with tableA ... or truncate data form tableA and insert from tableB.

Test!

Anurag Received on Mon Jul 28 2003 - 19:59:18 CDT