Re: Help: How do I group them together

From: Vitaliy Vorontsov <vitaliy.vorontsov_at_gmail.com>
Date: 16 Nov 2006 10:53:34 -0800
Message-ID: <1163703214.862220.225470@f16g2000cwb.googlegroups.com>

Your case is quite an unusual situation, but the solution may be the following:

1.Prepare the structure

create table t
(
id number,

value1 varchar2(1),
value2 varchar2(1),
value3 varchar2(1)

);

insert into t(id,value1,value2,value3)
  values (1,'A',null,null);

insert into t(id,value1,value2,value3)
  values (1,null,'B',null);
insert into t(id,value1,value2,value3)
  values (1,null,null,'C');

insert into t(id,value1,value2,value3)

  values (2,null,'A',null);
insert into t(id,value1,value2,value3)
  values (2,'D',null,null);
insert into t(id,value1,value2,value3)
  values (2,null,null,'E');

commit;

2. The following query would give you the neccessary result:

select i_t.id,

       (select max(value1) from t where t.id = i_t.id),
       (select max(value2) from t where t.id = i_t.id),
       (select max(value3) from t where t.id = i_t.id)
  from (select distinct id from t) i_t;

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.
Received on Thu Nov 16 2006 - 12:53:34 CST