

Archives of the TeradataForum
Message Posted: Fri, 10 Jan 2003 @ 08:53:58 GMT
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
 