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

 < 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

 < Last>>

Attachments

Library

Quick Reference

Archives

Sample Index

 2016 2007 2015 2006 2014 2005 2013 2004 2012 2003 2011 2002 2010 2001 2009 2000 2008 1999

2003 Indexes

 Jan Jul Feb Aug Mar Sep Apr Oct May Nov Jun Dec

 Top Home Privacy Feedback