Hey Alex,
Mujeeb fixed my errors (aliases)! The code (see below) is working
beautifully!

select case rn
when 1 then cast(empno as char(6))
when 2 then cast(lastname as char(15))
when 3 then cast(job as char(9))
when 4 then cast(salary as char(9))
end emps
from (select e.empno, e.lastname, e.job, e.salary, row_number()
over(partition by e.empno order by e.empno) rn
from employee e,
(select empno, lastname, job, salary
from employee) h ) f where rn in (1,2,3,4);


It's Fridayyyyyy! Partyyyyyyyyyy! Hehe.

Have a great Day!


-----Original Message-----
From: Alex Levy
[mailto:db2udbdba-ezmlmshield-x70723777.[Email address protected]
Sent: Thursday, November 15, 2007 9:01 AM
To: LazyDBA Discussion
Subject: RE: Converting Each Column to a Row

Robot Sniper keeps on shooting the URL. You may have to join to view it
-
membership is free - but IDUG's October 'Pick Of The Forums'* has an
excellent example of the recursive concatenation of multiple rows into
single columns.

There is only a limited explanation of the method and example, but the
reward is in taking the trouble to trace the SQL and work out the
intermediate result sets.

Regards
Alex

* Correct use of the Latin plural would see this site renamed to 'Pick
Of
The Fora'; however to mention such an error of grammar may appear
churlish
or excessively pedantic.

-----Original Message-----
From: Rai Amit
[mailto:db2udbdba-ezmlmshield-x86007390.[Email address protected]
Sent: 14 November 2007 15:49
To: LazyDBA Discussion
Subject: RE: Converting Each Column to a Row

Something like this .......

SELECT emp_no,
MAX(CASE WHEN rn = 1 THEN cast(empno as char(6)) END), MAX(CASE WHEN rn
= 2
THEN lastname END) , MAX(CASE WHEN rn = 3 THEN job END) , MAX(CASE WHEN
rn =
4 THEN cast(salary as char(9)) END) FROM (select e.empno, e.lastname,
e.job,
e.salary, e.workdept,
row_number() over(partition by e.empno order by e.empno) rn

from employee e ) t
GROUP BY t.empno;

________________________________

From: Edwards Ed [mailto:db2udbdba-ezmlmshield-x32874772.[Email address
protected]
Sent: Wed 11/14/2007 10:02 AM
To: LazyDBA Discussion
Subject: Converting Each Column to a Row



To All,
Using the code below, how can I eliminate the blank rows in the
output?

select case rn

when 1 then cast(empno as char(6))

when 2 then lastname

when 3 then job

when 4 then cast(salary as char(9))

when 5 then workdept

end emps

from (select e.empno, e.lastname, e.job, e.salary, e.workdept,
row_number() over(partition by e.empno order by e.empno) rn

from employee e,

(select empno, lastname, job, salary, workdept

from employee where job = 'CLERK') x

where e.workdept = 'A00') y;


Please take a few minutes to provide feedback on the quality of service
you
received from our staff. The Department of Education values your
feedback as
a customer. Commissioner of Education Jeanine Blomberg is committed to
continuously assessing and improving the level and quality of services
provided to you.Simply use the link below. Thank you in advance for
completing the survey.


http://data.fldoe.org/cs/default.cfm?staff=Ed.[Email address protected]


---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba
job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com To
unsubscribe: http://www.lazydba.com/unsubscribe.html



---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba
job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com To
unsubscribe: http://www.lazydba.com/unsubscribe.html





---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html


DB2 & UDB email list listserv db2-l LazyDBA home page