We have posted our answers with Charles at the same time :))) But I
must admit that Charles' solution is much more elegant.
Charles Hooper wrote:
> emdproduction_at_hotmail.com wrote:
> > I am using Oracle 9i.
> >
> > I have a table with rows like this:
> >
> > ID value1 value2 value3
> > 1 A
> > 1 B
> > 1 c
> > 2 A
> > 2 D
> > 2 E
> >
> > I would like the output like this:
> > ID value1 value2 value3
> > 1 A B c
> > 2 D A E
> >
> > Thanks for your help.
>
> I must be overlooking something that is not clear from your example:
> CREATE TABLE T1 (
> ID NUMBER(12),
> VALUE1 VARCHAR2(10),
> VALUE2 VARCHAR2(10),
> VALUE3 VARCHAR2(10));
>
> INSERT INTO
> T1
> VALUES (
> 1,
> 'A',
> NULL,
> NULL);
>
> INSERT INTO
> T1
> VALUES (
> 1,
> NULL,
> 'B',
> NULL);
>
> INSERT INTO
> T1
> VALUES (
> 1,
> NULL,
> NULL,
> 'c');
>
> INSERT INTO
> T1
> VALUES (
> 2,
> NULL,
> 'A',
> NULL);
>
> INSERT INTO
> T1
> VALUES (
> 2,
> 'D',
> NULL,
> NULL);
>
> INSERT INTO
> T1
> VALUES (
> 2,
> NULL,
> NULL,
> 'E');
>
> SELECT
> ID,
> MAX(VALUE1) VALUE1,
> MAX(VALUE2) VALUE2,
> MAX(VALUE3) VALUE3
> FROM
> T1
> GROUP BY
> ID
> ORDER BY
> ID;
>
> ID VALUE1 VALUE2 VALUE3
> 1 A B c
> 2 D A E
>
> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.
Received on Thu Nov 16 2006 - 12:58:22 CST
