SQL Pivot 2 fields

Hi @svrmhuebner,

Here’s a sample query that gets the same error as your query.

SELECT * FROM
   (SELECT cf$_workcenter10 AS production_line,
           order_no AS orders,
           revised_qty_due AS lot_size,
           objstate AS status
    FROM IFSAPP.SHOP_ORD_CFV
    WHERE UPPER(objstate) IN ('STARTED','RELEASED','PLANNED')
      AND UPPER(contract) = UPPER('&SITE'))
   PIVOT ( COUNT(orders), AVG(lot_size) FOR production_line IN ('2CYCL','DRUM','GAL','OFFLN','PAIL','QUART','TOTE','UTLTY'))

Here’s the modified query that’s working now using multiple pivots.

SELECT * FROM
   (SELECT cf$_workcenter10,
           order_no,
           revised_qty_due,
           objstate           
    FROM IFSAPP.SHOP_ORD_CFV
    WHERE UPPER(objstate) IN ('STARTED','RELEASED','PLANNED')
      AND UPPER(contract) = UPPER('&SITE'))
   PIVOT ( COUNT(order_no) AS ORDERS, AVG(revised_qty_due) AS LOT_SIZE FOR cf$_workcenter10 IN ('2CYCL','DRUM','GAL','OFFLN','PAIL','QUART','TOTE','UTLTY'))

I think if you modify your query as follows it will work.

SELECT * FROM
(SELECT account, 
        account_desc, 
        budget_period,amount,
        cf$_sp_actual_costs,
        budget_year,
        code_d
FROM budget_period_amount1_cfv 
WHERE code_d like nvl(upper('&DEPT'),'%')
  AND budget_year like nvl(upper('&YEAR'),'%')
  AND budget_version = 2)
PIVOT(SUM(amount),SUM(cf$_sp_actual_costs) FOR budget_period IN (1,2,3,4,5,6,7,8,9,10,11,12))

William Klotz

Many thanks William, the changes seemed to have worked.  Appreciate the help.

Mark