Archives of the TeradataForum

Message Posted: Fri, 10 Jan 2003 @ 00:54:27 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Try this the first part is your data:

create table a (c1 varchar (10), c2 integer);

create table b (c1 varchar (10), c2 varchar(10));
insert into a('abcd', 24);
insert into a('aaaa', 12);
insert into a('bbbb', 4);
insert into a('cccc', 3);

insert into b('abcd', 'k');
insert into b('abcd', 'l');
insert into b('abcd', 'g');
insert into b('abcd', 'f');
insert into b('aaaa', 'a');
insert into b('aaaa', 'b');
insert into b('aaaa', 'c');
insert into b('bbbb', 'o');
insert into b('bbbb', 'z');
insert into b('cccc', 'w');

------------------------------
-- create and load a temp table that assigns a known value (i.e. a
-- sequence number) to each of the table b records.
create volatile table btmp (
    c1 varchar(10),
    c2 varchar(10),
    seq integer)
on commit preserve rows;

insert into btmp (c1, c2, seq)
select c1, c2, csum (1, c2)
from b
group by c1;

-- Finally denormalise table b based upon the sequence number.
-- NULL values will be returned for table b entries where no data
-- exists. obviously you could return any other value using a case
-- statement eg
--  case when b1.c2 is null then '-' else b1.c2 end
-- etc
select a.c1, a.c2, b1.c2, b2.c2, b3.c2, b4.c2
from a left outer join btmp b1 on
    a.c1 = b1.c1 and
    b1.seq = 1
  left outer join btmp b2 on
    a.c1 = b2.c1 and
    b2.seq = 2
  left outer join btmp b3 on
    a.c1 = b3.c1 and
    b3.seq = 3
  left outer join btmp b4 on
    a.c1 = b4.c1 and
    b4.seq = 4
order by 1

Hope this helps

Glenn Mc

	<Prev	Next>		<<First	<Prev	Next>	Last>>