Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 26 Aug 2005 @ 02:09:51 GMT


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


Subj:   Re: Row to Column Function
 
From:   Michael Larkins

Partho:

Oops, after I hit SEND, I realized that there would still be multiple rows for each department in the derived table. So, this is more along the lines of what you need:

     sel a.deptno, a.job, a.loc, job1, job2, job3, job4, job5,
                                 job6, job7, job8, job9, job10

     FROM (sel deptno, max(frst), max(scnd), max(thrd), max(frth), max(ffth)
               , max(sxth), max(svth), max(egth), max(nith), max(tnth)
           FROM
             (sel deptno, case pos when 1 then job end, case pos when 2 then job end
                        , case pos when 3 then job end, case pos when 4 then job end
                        , case pos when 5 then job end, case pos when 6 then job end
                        , case pos when 7 then job end, case pos when 8 then job end
                        , case pos when 9 then job end, case pos when 10 then job end
          FROM
             (SELECT deptno, job, rank() over (partition by deptno order by job) as pos
              FROM (SELECT DISTINCT deptno, job FROM emp) dt) dt2)
         dt3 (deptno, frst, scnd, thrd, frth, ffth, sxth, svth, egth, nith, tnth) )
         dt4 (deptno, job1, job2, job3, job4, job5, job6, job7, job8, job9, job10) JOIN dept a
         ON dt4.dept=a.deptno;

See if this doesn't get you closer to what you are hoping to accomplish.

Michael Larkins
Certified Teradata Master
Certified Teradata SQL Instructor



     
  <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: 15 Jun 2023