Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 26 Aug 2005 @ 01:31:19 GMT


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


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

Partho:

You could do something like this:

     sel a.deptno, a.job, a.loc, 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 (dept, frst, scnd, thrd, frth, ffth, sxth, svth, egth, nith, tnth)
               JOIN dept a
                  ON dept=deptno;

I have not tested it so there could be a syntax error or two in it. Also, since you didn't specify the number, it assumes that there are never be more than 10 jobs in a single department. If there are more than 10, you will need to add enough case comparisons to max out the largest number of jobs in a single department and add a column name for dt3. It will always create a fixed length row even if several of the jobs are null.


Hope this helps,

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