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




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