Hey Rai,
Below is my original output.

000010
HAAS
PRES
0052750.0
A00

000110
LUCCHESSI
SALESREP
0046500.0
A00

000120
O'CONNELL
CLERK
0029250.0
A00

I am trying to eliminate the blank lines after each workdept number
(A00).






-----Original Message-----
From: Rai Amit
[mailto:db2udbdba-ezmlmshield-x25066616.[Email address protected]
Sent: Wednesday, November 14, 2007 3:11 PM
To: LazyDBA Discussion
Subject: RE: Converting Each Column to a Row

A liitle bit more code will give you one column...

SELECT emp_no,
case when (t1.Q_1 is null or t1.Q_1 = '') then '' else t.Q_1 concat ', '
end concat
case when (t1.Q_2 is null or t1.Q_2 = '') then '' else t.Q_2 concat ', '
end concat
case when (t1.Q_3 is null or t1.Q_3 = '') then '' else t.Q_3 concat ', '
end concat
case when (t1.Q_4 is null or t1.Q_4 = '') then '' else t.Q_4 end
FROM
(SELECT emp_no,
MAX(CASE WHEN rn = 1 THEN cast(empno as char(6)) END) as Q_1,
MAX(CASE WHEN rn = 2 THEN lastname END) as Q_2,
MAX(CASE WHEN rn = 3 THEN job END) as Q_3,
MAX(CASE WHEN rn = 4 THEN cast(salary as char(9)) END) as Q_4
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)t1;


________________________________

From: Edwards Ed [mailto:db2udbdba-ezmlmshield-x1969442.[Email address
protected]
Sent: Wed 11/14/2007 2:39 PM
To: LazyDBA Discussion
Subject: RE: Converting Each Column to a Row



Hey Rai,
Your output creates 5 columns. I only want one column as output.


-----Original Message-----
From: Rai Amit
[mailto:db2udbdba-ezmlmshield-x86007390.[Email address protected]
Sent: Wednesday, November 14, 2007 10:49 AM
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



---------------------------------------------------------------------
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