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!

Row to Column Transformation

Discussion in 'SQL PL/SQL' started by rana.rajnikant, Feb 28, 2009.

  1. Hello to all.

    I am very thankful to club-oracle.

    I have following tables in production environment.
    Code (Text):

    *PropertyMaster
    PID PName
    1   Watch
    2   Bike
    3   Car
    4   Lap Top
    5   Mobile
    6   REFRIGRATOR
    7   WASHING MACHINE
     
    * THERE WILL BE N NUMBERS OF PROPERY ID
    ---------------------------
    Code (Text):
    *MemberMaster  
    MID Name
    1   XYZ
    2   PQR
    3   ABC
     
    *PropertyDet (references Property Master)
    Code (Text):
    MId PID PValue
    1   2   YAMAHA
    1   1   FAST TRACK
    1   3   SANTRO
    1   4   DELL
    1   5   SONY ERIC.
    2   1   TITAN
    2   3   SONATA
    2   4   SONY
    2   2   BAJAJ
    2   5   NOKIA
    1   6   SAMSUNG
    2   7   LG
    ---------------------------------------
    --
    From using above tables, I want out put in below format

    My Desire Output would
    Code (Text):
    MID WATCH   BIKE    CAR LAPTOP  MOBILE  REFRIGRATOR WASHINGMACHINE
    1   FAST TRACK  YAMAHA  SANTRO  DELL    SONY ERIC.  SAMSUNG "Null"
    2   TITAN   BAJAJ   SONATA  SONY    NOKIA   "Null"  LG


    can anybody help on this???

    I want all property type to be converted in column for each member.

    Thanks in advance
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
  3. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    do this,

    this will work.
    Code (Text):

    SELECT   mid, NVL (MAX (DECODE (pname, 'Watch', pvalue)), 0) watch,
             NVL (MAX (DECODE (pname, 'Bike', pvalue)), 0) bike,
             NVL (MAX (DECODE (pname, 'Car', pvalue)), 0) car,
             NVL (MAX (DECODE (pname, 'LapTop', pvalue)), 0) laptop,
             NVL (MAX (DECODE (pname, 'Refrigrator', pvalue)), 0) refrigrator,
             NVL (MAX (DECODE (pname, 'Mobile', pvalue)), 0) mobile,
             NVL (MAX (DECODE (pname, 'Washing Machine', pvalue)),
                  0
                 ) washing_machine
        FROM (SELECT pd.mid mid, pm.pname pname, pd.pvalue pvalue
                FROM propertydet pd, propertymaster pm
               WHERE pd.pid = pm.pid) prop
    GROUP BY mid
     
    The output is
    Code (Text):

    MID WATCH BIKE CAR LAPTOP REFRIGRATOR MOBILE WASHING_MACHINE

    1 FASTTRACK YAMAHA SANTRO DELL SAMSUNG SONY ERIC 0
    2 TITAN BAJAJ SONATA SONY 0 NOKIA LG
     
     
  4. r.uma

    r.uma Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    hai,in this program i am facing performance issue.....because of more records ......for 5000 rows its taking 59 sec....but i have milllon records in the original table ............
    itz taking much time how to avoid this problem help me out in this....

    DECLARE
    Rec_count_for_commit NUMBER := 0;
    Rec_count_for_commit1 NUMBER := 0;
    BEGIN
    for cur_oos_var in (
    SELECT item_upc,avail_to_sell,loc_nbr
    from ft_curr_ent_bal1
    where item_upc>1 and
    LOC_NBR in (975,525,526,1385,3831,4421) group by

    item_upc,avail_to_sell,loc_nbr)
    loop
    Rec_count_for_commit := Rec_count_for_commit + 1;
    --BEGIN THE oos_loc_date UPDATION
    BEGIN
    if(cur_oos_var.avail_to_sell<1) then
    UPDATE ft_ceb_onhand1 SET OOS_LOC_DATE=sysdate
    WHERE oos_loc_date is null and item_upc=cur_oos_var.item_upc and

    loc_nbr=cur_oos_var.loc_nbr;
    else
    UPDATE ft_ceb_onhand1
    SET oos_loc_date=null
    WHERE oos_loc_date is not null and item_upc=cur_oos_var.item_upc and

    loc_nbr=cur_oos_var.loc_nbr;
    end if;
    If Rec_count_for_commit = 5000 Then
    Rec_count_for_commit := 0;
    Commit;
    End If;
    end;
    end loop;
    --BEGIN THE oos_network_date UPDATION
    for cur_OOS_net_var in ( select item_upc from ft_curr_ent_bal1 where

    item_upc>1 and avail_to_sell<1 and
    LOC_NBR in (975,525,526,1385,3831,4421) group by item_upc

    having count(loc_nbr)=6)
    loop
    Rec_count_for_commit1 := Rec_count_for_commit1 + 1;
    UPDATE ft_ceb_onhand1
    SET OOS_NETWORK_DATE=sysdate
    WHERE item_upc =cur_OOS_net_var.item_upc;
    /* update ft_ceb_onhand1
    set oos_network_date = Null
    where avail_to_sell >0 AND LOC_NBR IN (975,525,526,1385,3831,4421);*/


    If Rec_count_for_commit1 = 5000 Then
    Rec_count_for_commit1 := 0;
    Commit;
    End If;
    end loop;
    end;
    /