Re: SQL Pivot

On Wed 05-May-2011 10:38 , Jack Prucha wrote:

I'm trying to join a master record to a detail file in which there
may be 0 - 2 matching records and turn it on it's side. I can easily
do this if there are always two matching records but can't figure out
how to handle the other two situations (no matching or just one
matching).

The end result should look like this:

Acct#, Day1, Day2

Master record has account# and other needed information for final
results.

Detail record has account# and Day which is 1 -31.

Results could like this:

Acct#, Day1, Day2
1 1 15
2 1 0
3 0 0

Day1 should be lesser of both days.

It's straightforward if both detail records are there using group by
with Min and Max. We're still on 5.4 and at least a month away from
7.1.

If I understood correctly, the following example sets up TABLE M as Master, TABLE D as Detail, and TABLEs R# as results for reporting.

A SQL script; e.g. run with RUNSQLSTM qsqlsrc SRCMBR(_code_) COMMIT(*NONE) NAMING(*SYS) ERRLVL(30)

<code>

set current schema qtemp ;
drop table m ;
create table m (a dec, b char) ;
insert into m values(1, 1),(2, 2),(3, 3),(4, 4),(5, 5) ;
drop table d ;
create table d (a dec, d dec(2)) ;
insert into d values(1, 1),(2, 1),(1, 2),(1, 4),(1,15) ;
drop table r1 ;
create table r1(a dec, d1 dec(2), d2 dec(2)) ;
insert into r1
( select m.a,(select min(d) from d where d.a=m.a)
, nullif((select max(d) from d where d.a=m.a)
,(select min(d) from d where d.a=m.a))
from m ) ;
call qsys/qcmdexc('runqry *n qtemp/r1', 0000000018.00000) ;
drop table r2 ;
create table r2(a , d1 , d2 ) as
( with dgrp (a, d1, d2) as
(select a, min(d), max(d) from d group by a)
select m.a, d.d1, nullif(d.d2,d.d1)
from m left join dgrp as d on m.a=d.a ) with data ;
call qsys/qcmdexc('runqry *n qtemp/r2', 0000000018.00000) ;

</code>

For both, the effect is a report [though unordered] like:

<code>

A D1 D2
000001 1 1 15
000002 2 1 -
000003 3 - -
000004 4 - -
000005 5 - -
****** ******** End of report ********

</code>

Regards, Chuck