Home » SQL & PL/SQL » SQL & PL/SQL » SELECT dates into Different Columns - A better way?
SELECT dates into Different Columns - A better way? [message #259508] Wed, 15 August 2007 15:05 Go to next message
hdogg
Messages: 93
Registered: March 2007
Member
Structure in Monthly Job Revenue

Month     Revenue  Jobcode
01/31/07 209220   45456
02/28/07 2233221  45664
...ETC


Query A Structure (new table)

Jobcode January February March...etc
      45456    5644     54545    544
      45457    6654545  45545    54545

As you can see I have been selecting each month and creating a new column for each one. It takes a join for each one and tons of select statements....


Is there a better way to create these columns????


Query A


SELECT a3.jobcode,
  january,
  february
FROM
  (SELECT a1.jobcode,
     SUM(january) AS
  january,SUM(july) AS
  july

   FROM
    (SELECT mtdrev AS
     january,
       jobcode
     FROM forecast_data
     WHERE to_char(to_date(period,    'mm/dd/rr'),    'mm') = '01')
  a1,
      (SELECT jobcode,
       mtdrev AS
     july
     FROM forecast_data
     WHERE to_char(to_date(period,    'mm/dd/rr'),    'mm') = '02')
  a2
   WHERE a1.jobcode = a2.jobcode
   GROUP BY a1.jobcode)
a3


Re: SELECT dates into Different Columns - A better way? [message #259509 is a reply to message #259508] Wed, 15 August 2007 15:06 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
search for "pivot query"

New in V11
SELECT EXTRACT(YEAR FROM order_date) year, order_mode, order_total FROM orders)
PIVOT
(SUM(order_total) FOR order_mode IN ('direct' AS Store, 'online' AS Internet));

[Updated on: Wed, 15 August 2007 15:57] by Moderator

Report message to a moderator

Re: SELECT dates into Different Columns - A better way? [message #259516 is a reply to message #259509] Wed, 15 August 2007 15:39 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Oh crap, you beat me to it.
I noted in the 11G docs about the new PIVOT command (thanks for pointing out that the docs were updated).
It's going to be fun to now answer the daily question about pivoting with this kind of response, when the poster does not mention their Oracle version. I know Michel is going to have a field day with this.


But I think your parenthesis are incorrect in your example. I don't have an 11g database to try this out yet.

[Updated on: Wed, 15 August 2007 15:42]

Report message to a moderator

Re: SELECT dates into Different Columns - A better way? [message #259517 is a reply to message #259509] Wed, 15 August 2007 15:41 Go to previous message
hdogg
Messages: 93
Registered: March 2007
Member
Thanks!!!!! I found out i dont have 11 yet..... So i found a link for everyone that gives an example of how to do it....

http://www.adp-gmbh.ch/ora/sql/examples/pivot.html
Previous Topic: Partition index analyze
Next Topic: read a text file and insert into db
Goto Forum:
  


Current Time: Sat Dec 03 22:03:27 CST 2016

Total time taken to generate the page: 0.08867 seconds