Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Location
    London
    Posts
    76

    Unanswered: SQL in Oracle 9i, merging columns

    Hi, I have the following query:

    select c.CUSTOMERNO, dm.INVOICEMONTH,
    sum(dm.PRODUCTCOUNT1) as PORTAL_VOL
    from debmonthly dm, monitororder mo, customer c, employee e
    where mo.ORDERNO = dm.ORDERNO
    and mo.CUSTOMERNO = c.CUSTOMERNO
    and c.SALESPERSONID = e.USERID
    and c.SALESPERSONID in (3961,1074,5122)
    and dm.PRODUCTNO in (8285,1287,2287,3287,1387,2387,3387,1289)
    group by c.CUSTOMERNO, dm.INVOICEMONTH,

    which provides the following example results:

    CUSTOMERNO INVOICEMONTH PORTAL_VOL
    1 May 2004 10
    1 Jun 2004 15
    2 May 2004 50
    2 Jun 2004 75
    2 Jul 2004 100
    3 Jun 2004 5
    etc...

    I want to be able to have another column within the same result set showing different product volumes, i.e.:

    CUSTOMERNO INVOICEMONTH EMAIL_VOL FAX_VOL
    1 May 2004 10 2
    1 Jun 2004 15 0
    1 Jul 2004 0 10
    2 May 2004 50 5
    2 Jun 2004 75 0
    2 Jul 2004 100 10
    3 May 2004 0 25
    3 Jun 2004 5 0
    etc...

    How can I acheive this?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Sorry, maybe I'm being dense, but what does this new column represent?

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs down

    Quote Originally Posted by andrewst
    Sorry, maybe I'm being dense, but what does this new column represent?
    andrewst Don't you recognize the same old 'crosstab/pivot' table scene?
    Seem's every day there is someone lazy not doing any research, tossing out the same question over and over and over again in every single forum in existence.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Right you are - I didn't notice that the 3rd column's header had changed!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •