Archives of the TeradataForum

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

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

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>>