Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 10 Jan 2003 @ 08:53:58 GMT


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


Subj:   Re: Please help
 
From:   Dieter Nöth

Hi Glenn,

instead of creating a table and doing lots of outer joins, you can simply use a derived table and a group by. This is much more efficient and easier to write:

select
   a.c1
   ,a.c2
   ,max(case when seq = 1 then dt.c2 end) as "1"
   ,max(case when seq = 2 then dt.c2 end) as "2"
   ,max(case when seq = 3 then dt.c2 end) as "3"
   ,max(case when seq = 4 then dt.c2 end) as "4"
   ,max(case when seq = 5 then dt.c2 end) as "5"
from
   a
left join
   (select
      c1
      ,c2
      ,sum(1) over (partition by c1 order by c2
                    rows unbounded preceding) as seq
    from b
   ) dt
on a.c1 = dt.c1

group by
   a.c1
   ,a.c2
order by a.c1
;

It's still just solving a part of Sreya's problem, but to do all that dynamically you'll have to use a SP ;-)

Dieter



     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016