Hi Ed
You're missing my point - this SQL may or may not produce the correct
result, but the logic is obscure and possibly impenetrable; whereas a
general solution to a general problem, such as that posted in IDUG, is
nearly always more flexible and adaptable. The row-numbering SQL is going
round the houses: for example, what is the purpose of joining the employee
table to an ordered subset of itself without any join condition? Why is
there a need for a cartesian join of the table expressions rn and h? If
EMPLOYEE is a large table, the cartesian join will slay performance. If the
purpose is simply to display each column on a new line, we could SELECT a
new line instead! So, on Windoze:
db2 -x "with crlf (cr,lf) as (values(chr(13),chr(10)))
select empno,cr,lf,
lastname,cr,lf,
job,cr,lf,
workdept,cr,lf,
digits(salary)
from employee,crlf"
will do a quicker and simpler job, without most of the casting.
This doesn't rule out that the row-numbering cartesian approach might be a
useful technique for some tasks, but not likely this one.
OK, it's Saturday - let's go back to the party !!!
Regards
Alex Levy
Sustainable Software Ltd.
-----Original Message-----
From: Edwards Ed
[mailto:db2udbdba-ezmlmshield-x97735178.[Email address protected]
Sent: 15 November 2007 17:43
To: LazyDBA Discussion
Subject: RE: Converting Each Column to a Row
Alex,
Also, if you run this code, it displays all of the data for the four
columns from the case statement for all records in the applicable table.
If you want to display the data from all columns in the applicable
table, all you have you do is add the applicable columns to the case
statement (see code below - I added the "workdept" column).
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(workdept as char(3))
when 5 then cast(salary as char(9))
end emps
from (select e.empno, e.lastname, e.job, e.workdept, e.salary,
row_number() over(partition by e.empno order by e.empno) rn
from employee e,
(select empno, lastname, job, workdept, salary
from employee) h ) f where rn in (1,2,3,4, 5);
-----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
---------------------------------------------------------------------
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