Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2016
    Posts
    1

    Post Unanswered: pivot a sql statement

    Hi

    fairly new to SQL so please forgive any naivety

    I have a statement that produces too many rows for excel, so I am trying to trim the results.
    easiest way to do this would be place month and year as a column heading not in the row results (basically pivoting the results)

    this is what I currently have and a partial results layout. I have tried putting a pivot function on the end but I fail everytime

    thanks for any help,

    Dan

    select tc.month||'-'||tc.year,PH.LEVEL2_CD,CM.BPCS_CHNL , SUM(HI.ORDER_QTY)
    from history HI, t_calendar tc, part_facility pf, cust_master CM, product_hier PH
    where PF.PROD_CD = PH.PROD_CD
    and hi.sup_partno = PF.PARTNO
    and HI.CUST_ACC_NO = CM.CUST_ACC_NO
    and HI.demand_dt between tc.from_date and tc.to_date
    and hi.bpcs_system = 'EU'
    and pf.fac_cd IN ('XX')
    and HI.DEMAND_WH in ('XX','XX')
    AND HI.SPLIT_NO = 1
    and HI.SUP_PARTNO = 'XXX'
    and PF.CSS_CD IN ('Q','A','B','C','K')
    and HI.DEMAND_DT > TO_DATE ('01/07/2014','DD/MM/YYYY')
    and HI.DEMAND_DT < TO_DATE ('01/07/2016','DD/MM/YYYY')
    AND CM.BPCS_CHNL in ('X')
    GROUP BY tc.month||'-'||tc.year,PH.LEVEL2_CD,CM.BPCS_CHNL



    TC.MONTH||'-'||TC.YEAR LEVEL2_CD BPCS_CHNL SUM(HI.ORDER_QTY)
    Feb-15 X X 326
    Nov-14 X X 362
    Dec-14 X X 1348
    Apr-15 X X 1069

  2. #2
    Join Date
    Sep 2016
    Location
    Pune
    Posts
    16
    Hi , I have little knowledge about pivot sql statement ,
    Pivot query help us to generate an interactive table that quickly combines and compares large amounts of data. We can rotate its rows and columns to see different summaries of the source data, and we can display the details for areas of interest at a glance. It also help us to generate Multidimensional reporting.
    Thank You !!

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Quote Originally Posted by snehasingh9012
    I have little knowledge about pivot sql statement
    Maybe, but your copy/paste skills are excellent. Only if you shared the source you "stole" that article from ... For more info, click here.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    that is not a pivot select, Please see the following link for information on using it

    http://www.oracle.com/technetwork/ar...ot-097235.html
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Tags for this Thread

Posting Permissions

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