Re: SQL Pivot

[CRPence <CRPbottle@xxxxxxxxx>]

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