RE: Converting Each Column to a Row

RE: Converting Each Column to a Row

 

  

Below is the output from the code below.

000010 000010,
000020 000020,
000030 000030,
000050 000050,
000060 000060,
000070 000070,
000090 000090,
000100 000100,
000110 000110,
000120 000120,
000130 000130,
000140 000140,
000150 000150,
000160 000160,
000170 000170,
000180 000180,
000190 000190,
000200 000200,
000210 000210,
000220 000220,
000230 000230,
000240 000240,
000250 000250,
000260 000260,
000270 000270,
000280 000280,
000290 000290,
000300 000300,
000320 000320,
000330 000330,
000340 000340,

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

a correction....

SELECT emp_no,
case when (t1.Q_1 is null or t1.Q_1 = '') then '' else t1.Q_1 concat ',
' end concat
case when (t1.Q_2 is null or t1.Q_2 = '') then '' else t1.Q_2 concat ',
' end concat
case when (t1.Q_3 is null or t1.Q_3 = '') then '' else t1.Q_3 concat ',
' end concat
case when (t1.Q_4 is null or t1.Q_4 = '') then '' else t1.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: Rai Amit [mailto:db2udbdba-ezmlmshield-x25066616.[Email address
protected]
Sent: Wed 11/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



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