Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: pivot/transform in Oracle
I used the MAX function and got a little closer, but I lost something
in the translation...
My source is:
DATE DESC START_TIME LOCATION_ID NAME 2006-01-25 WATCH 1 00:00 3 CHARLES D'ARCY 2006-01-25 WATCH 2 08:00 3 JOE FRIDAY 2006-01-25 WATCH 3 16:00 3 JAMES ROBIN 2006-01-26 WATCH 1 00:00 3 CHARLES D'ARCY 2006-01-26 WATCH 2 08:00 3 JOE FRIDAY 2006-01-26 WATCH 3 16:00 3 JAMES ROBIN 2006-01-27 WATCH 1 00:00 3 JAMES ROBIN 2006-01-27 WATCH 1 00:00 3 CHARLES D'ARCY 2006-01-27 WATCH 2 08:00 3 JOE FRIDAY 2006-01-27 WATCH 3 16:00 3 SECURITY MAN 2006-01-28 WATCH 1 00:00 3 CHARLES D'ARCY 2006-01-28 WATCH 2 08:00 3 JOE FRIDAY 2006-01-28 WATCH 3 16:00 3 JAMES ROBIN 2006-01-28 WATCH 3 16:00 3 SECURITY MAN
>From this source I would like to see...
DESC START_TIME SUNDAY MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY SATURDAY WATCH 1 00:00 CHARLES D'ARCY CHARLES D'ARCY CHARLES D'ARCY CHARLES D'ARCY WATCH 1 00:00 JAMES ROBIN WATCH 2 08:00 JOE FRIDAY JOE FRIDAY JOE FRIDAY JOE FRIDAY WATCH 3 16:00 JAMES ROBIN JAMES ROBIN SECURITY MAN JAMES ROBIN WATCH 3 16:00 SECURITY MAN
sorry this wraps, but...
Notice that I get two lines for watch 1 because there are two people on
Friday and two on Watch 3 'cause there are two on Saturday.
With MAX I lose the two extra lines. And without max I get a line for every name. I hope there's a happy medium....
My alternative is to just write code to loop through the recordset and
position the items in the proper cells.
And, to be perfectly honest, I'm not sure this can be done with SQL.
(I will continue to search through asktom, although I can't seem to get
there from work....)
DA Morgan wrote:
> oaksong_at_hotmail.com wrote:
>
> As Anurag suggests search at asktom.oracle.com. Also look at the demos
> in Morgan's Library at www.psoug.org.
> --
> Daniel A. Morgan
> http://www.psoug.org
> damorgan_at_x.washington.edu
> (replace x with u to respond)
Received on Fri Jan 27 2006 - 10:21:04 CST