1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Problem with SQL Query with PIVOT

Discussion in 'SQL PL/SQL' started by mickey, Jul 31, 2012.

  1. mickey

    mickey Guest

    Guys,

    I have seen a lot of post evolving around in this forum regarding PIVOT.
    I have a problem here with the PIVOT. I am not 100% sure if its possible this way though.

    I felt the experts here can guide me thru..

    Below is the query . I also have to get the sum for the char type not in 'FS and F'. Not sure if i can add another condition to PIVOT as I am now aware of the restrictions to PIVOT.

    I gave a try but unfortunately not able to succeed with PIVOT in this below query.


    Code (SQL):


    SELECT *
    FROM (SELECT ch.carnam,
    cdl.cartyp,
    cm.*,
    tc.chrg_amt,
    tc.chargetype
    FROM shipment s INNER
    JOIN sm ON s.ship_id = sm.ship_id INNER
    JOIN cm ON sm.car_move_id = cm.car_move_id INNER
    JOIN stp ON cm.car_move_id = stp.car_move_id INNER
    JOIN ch ON cm.carcod = ch.carcod INNER
    JOIN cdl ON ch.carcod = cdl.carcod LEFT OUTER
    JOIN tc
    ON (cm.car_move_id = tc.car_move_id AND tc.chrg_role = 'COST' AND tc.ignr_flg != 1 AND tc.chrg_level = 'ESTIMATED' AND ((tc.rate_serv_nam IS NULL) OR (tc.carcod = cm.carcod AND tc.rate_serv_nam = cm.rate_serv_nam)))
    WHERE 1 = 1
    AND cm.tndr_sts IN ('ASSIGNED', 'COMPLETE', 'MANUAL')
    AND to_date(substr(stp.pln_dep_dte, 0, 8)) >= @from_date
    AND to_date(substr(stp.pln_dep_dte, 0, 8)) <= @to_date
    AND @+cm.carcod
    AND @+cm.srvlvl
    AND stp.tms_stop_seq = 1) PIVOT(SUM(chrg_amt) FOR(chargetype) IN ('FS' AS f, 'F' AS fr))

     
    Your help is highly appreciated.

    Thanks in advance,
    Mickey
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Will you please post the create table statement and some sample data?