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!

Transpose

Discussion in 'SQL PL/SQL' started by ketangarg86, Apr 28, 2015.

  1. ketangarg86

    ketangarg86 Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Hi,

    I have a sql query that get the data as below.

    Month Clients Orders
    March 100 200
    April 200 300

    I want the results like this
    Month Clients Orders
    March 100 200
    April 200 300
    % inc 100% 50%

    Furthermore if possible can I get the results set finally as
    Month March April %inc
    Clients 100 200 100%
    Orders 200 300 50%
     
  2. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    What you have asked for -- either option -- won't work very well if you have more than two months. The models you provided don't have any way of displaying the percentage changes from the 2nd to 3rd month, 3rd to 4th month, and so on. The following example uses the LAG function to pull the clients/orders value from the previous line in the query and perform a percentage increase (or decrease) calculation:


    Code (Text):
    CREATE TABLE cli_ord_tmp (
    month_col  DATE,
    clients    NUMBER,
    orders     NUMBER);

    INSERT INTO cli_ord_tmp VALUES ('01-JAN-2105', 100, 200);
    INSERT INTO cli_ord_tmp VALUES ('01-FEB-2105', 200, 300);
    INSERT INTO cli_ord_tmp VALUES ('01-MAR-2105', 180, 250);
    INSERT INTO cli_ord_tmp VALUES ('01-APR-2105', 250, 400);
    INSERT INTO cli_ord_tmp VALUES ('01-MAY-2105', 300, 450);
    INSERT INTO cli_ord_tmp VALUES ('01-JUN-2105', 280, 420);
    INSERT INTO cli_ord_tmp VALUES ('01-JUL-2105', 400, 500);

    SELECT MONTH, clients, orders,
           TRUNC(DECODE(clients_lag, 0, 0, (clients - clients_lag) / clients_lag * 100), 2) AS cli_inc_pct,
           TRUNC(DECODE(orders_lag, 0, 0, (orders - orders_lag) / orders_lag * 100), 2) AS ord_inc_pct
    FROM
    (
    SELECT TO_CHAR(month_col, 'Month') MONTH,
           clients,
           orders,
           LAG(clients, 1, 0) OVER (ORDER BY month_col) AS clients_lag,
           LAG(orders, 1, 0) OVER (ORDER BY month_col) AS orders_lag
    FROM   cli_ord_tmp
    ORDER BY month_col
    );

    MONTH         CLIENTS ORDERS CLI_INC_PCT ORD_INC_PCT
    ------------- ------- ------ ----------- -----------
    January           100    200           0           0
    February          200    300         100          50
    March             180    250         -10      -16.66
    April             250    400       38.88          60
    May               300    450          20        12.5
    June              280    420       -6.66       -6.66
    July              400    500       42.85       19.04
     
  3. ketangarg86

    ketangarg86 Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Thanks. I will always have only 2 months to compare but I want the result in the following format. Basically I am performing two functions, 1-transposing, 2-calculating percentages. Would really appreciate your help


    MONTH CLIENTS ORDERS
    ------------- ------- ------ ----------- -----------
    January 100 200
    February 200 300

    MONTH JANUARY FEBRUARY %INC/DEC
    ------------- ------- ------ ----------- -----------
    CLIENTS 100 200 100
    ORDERS 200 300 50
     
  4. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    If you will always only have two months and your data is as simple as indicated, then this really looks like a homework problem. Provide the information to create and populate the table and the SQL queries you have tried so far. Alternately, search for 'oracle crosstab query' (which is what the 'transpose' operation you are requesting is called).