|
|
Archives of the TeradataForum
Message Posted: Thu, 26 Aug 2005 @ 02:09:51 GMT
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
| |