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,
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
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 !!