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