This discussion is archived
13 Replies Latest reply: Aug 1, 2013 3:20 AM by Raj_2013 RSS

How to split one row into 2 columns?

Raj_2013 Newbie
Currently Being Moderated

Hi,

 

 

I have a table with 4 columns and 1 row as below.  I need to write a query to  split the data in single row to two columns.

 

Your help would be very grateful for me.

 

Actual_Inventory      Actual_scrap     Planned_Inventory    Planned_Scrap

          1000                    2000               1400                    2500

 

 

I need to convert table as below.

 

 

                           Actual_Data            Planned_Data

 

                              1000                          1400  

 

                              2000                          2500

 

Thanks&Regards,

Raj

 

Message was edited by: Raj_2013

  • 1. Re: How to split one row into 2 columns
    Pacmann Journeyer
    Currently Being Moderated

    Hi,

     

    SELECT CASE l WHEN 1 THEN Actual_Inventory ELSE Actual_scrap END Actual_Data

    , CASE l WHEN 1 THEN Planned_Inventory ELSE Planned_scrap END Planned_data

    FROM yourtable

    CROSS JOIN (SELECT level l

                         FROM DUAL

                         CONNECT BY level <= 2)

  • 2. Re: How to split one row into 2 columns
    Vite DBA Pro
    Currently Being Moderated

    Hi Raj,

     

    you could try a Union query. IE

     

    select actual_inventory as actual_data, planned_inventory as planned_data from your_table

    UNION

    select actual_scrap, planned_scrap from your_table;

     

    Also, if possible you could try changing your data model so that it doesn't violate first normal form and remove the repeating groups. Rather than four columns you could have three

     

    data_type actual_data planned_data

     

    where data_type will be either scrap or inventory

     

    Regards

    Andre

  • 3. Re: How to split one row into 2 columns
    Raj_2013 Newbie
    Currently Being Moderated

    Hi Andre,

     

    Thanks for your response.

     

    It is working for 4 columns but My requirement is different. I am describing  my requirement below please help me.

     

     

    I have 5 tables like Inventory ,Scrap,FinishedGoods, Thruput, Spending

     

     

    and each having 2 columns as below.

     

     

     

     

     

    TABLES                                                        COLUMNS      

                                                                  

     

     

    Inventory                              ActualInventory,                PlannedInventory               Plant         

     

     

    Scrap                                  ActualScrap,                     PlannedScrap                     Plant              

     

     

    FinishedGoods                     ActualFinishedGoods,       PlannedFinishedGoods        Plant

     

     

    Thruput                                ActualThruput,                  PlannedThruput                   Plant                                                           

     

     

    Spending                             ActualSpending,                PlannedSpending                Plant      

     

                

     

     

     

     

     

     

    I need to calculate avg of each column and display  as two columns(USER DEFINED COLUMNS) as below

     

     

    ActualData        PlannedData

     

      45                           66   

      66                           78   

      88                           33                   

      15                           24         

      99                           77

     

     

     

     

     

    I have written query as below to calculate avg's of all columns

     

     

     

     

    Select avg(ActualInventory) ,avg(ActualScrap),avg(ActualFinishedGoods),avg(ActualThruput),avg(ActualSpending),

     

     

      avg(PlannedInventory),avg(PlannedScrap),avg(PlannedFinishedGoods),avg(PlannedThruput),avg(PlannedSpending)

     

     

      From  Inventory  i, Scrap s, FinishedGoods f, Thruput t, Spending sp

     

      where i.plant=s.plant and s.plant=f.plant and t.plant=sp.plant;

     

     

    I am getting result in 10 columns and 1 single row but My requirement is  need to display all 10 columns data

     

     

    into 2 columns as below.

     

     

     

    ActualData        PlannedData

     

     

      45.666                66.55    

      66                           78   

      88                           33                   

      15                           24         

      99                            77

     

     

    Thanks&Regards,

    Raj.

  • 4. Re: How to split one row into 2 columns
    KjetilSkotheim Newbie
    Currently Being Moderated

    You should be able to deduce a solution from what ViteDBA wrote. You just need 5 selects with union between them (or rather, use UNION ALL instead of UNION to avoid trouble...).

  • 5. Re: How to split one row into 2 columns
    Hoek Guru
    Currently Being Moderated

    Next time you have a question like this, then post CREATE TABLE and INSERT INTO statements, a small testcase, along with your exact database version.

    It is explained here: https://forums.oracle.com/message/9362002#9362002

     

    Now, this (simplified) example might give you ideas:

    SQL> create table t1 as select 'A' col, level planned, level * 10 actual from dual connect by level <= 10;
    Table created.
    SQL> create table t2 as select 'A' col, level/2 planned, level/2 * 10 actual from dual connect by level <= 10;
    Table created.
    SQL> create table t3 as select 'A' col, level/3 planned, level/3 * 10 actual from dual connect by level <= 10;
    Table created.
    SQL> create table t4 as select 'A' col, level/4 planned, level/4 * 10 actual from dual connect by level <= 10;
    Table created.
    SQL> create table t5 as select 'A' col, level/5 planned, level/5 * 10 actual from dual connect by level <= 10;
    Table created.
    SQL> --
    SQL> with helper as ( select 1 r from dual union
      2                   select 2 r from dual union
      3                   select 3 r from dual union
      4                   select 4 r from dual union
      5                   select 5 r from dual 
      6                 )
      7  select case 
      8           when r = 1 then act1
      9           when r = 2 then act2
     10           when r = 3 then act3
     11           when r = 4 then act4
     12           when r = 5 then act5
     13         end actual
     14  ,      case 
     15           when r = 1 then pla1 
     16           when r = 2 then pla2 
     17           when r = 3 then pla3 
     18           when r = 4 then pla4 
     19           when r = 5 then pla5 
     20         end planned
     21  from   helper
     22  ,      ( select avg(t1.actual) act1
     23           ,      avg(t2.actual) act2
     24           ,      avg(t3.actual) act3
     25           ,      avg(t4.actual) act4
     26           ,      avg(t5.actual) act5
     27           ,      avg(t1.planned) pla1
     28           ,      avg(t2.planned) pla2
     29           ,      avg(t3.planned) pla3
     30           ,      avg(t4.planned) pla4
     31           ,      avg(t5.planned) pla5
     32           from   t1
     33           ,      t2
     34           ,      t3
     35           ,      t4
     36           ,      t5
     37           where  t1.col = t2.col
     38           and    t1.col = t3.col
     39           and    t1.col = t4.col
     40           and    t1.col = t5.col
     41         );
        ACTUAL    PLANNED
    ---------- ----------
            55        5.5
          27.5       2.75
    18.3333333 1.83333333
         13.75      1.375
            11        1.1
    5 rows selected.
  • 6. Re: How to split one row into 2 columns
    Vite DBA Pro
    Currently Being Moderated

    Hi Raj,

     

    it still seems to me that you would be best served by normalising your database. Without any understanding of your business requirements, these tables seem to be very similar. As I discussed in my first post, you should merge the tables into one table with columns such as

     

    data_type, actual_data, planned_data, plant

     

    data_type would represent the separate table descriptions inventory, scrap, FinishedGoods, ThruPut, Spending.

     

    This would make your queries and data analysis much easier.

     

    Regards

    Andre

  • 7. Re: How to split one row into 2 columns
    Mohan1312 Explorer
    Currently Being Moderated

    Hi,

     

    select avg(actual_inventory) actual_data,avg(planned_inventory) planned_data from inventory

    union

    select avg(actual_scrap),avg(planned_scrap) from scrap

    union

    select avg(actual_finishedgoods),avg(planned_finishedgoods) from finishedgoods

    union

    select avg(actual_thruput),avg(planned_thruput) from thruput

    union

    select avg(actual_spending),avg(planned_spending) from spending;

     

    Regards,

    Mohan.

  • 8. Re: How to split one row into 2 columns
    Raj_2013 Newbie
    Currently Being Moderated

    Hi,

     

    My requirement is almost fulfilled. But I am getting the results in ascending order.

     

    But I need the results in same order how i have given selects statements ie

     

    select avg(actual_inventory) actual_data,avg(planned_inventory) planned_data from inventory

    union

    select avg(actual_scrap),avg(planned_scrap) from scrap

    union

    select avg(actual_finishedgoods),avg(planned_finishedgoods) from finishedgoods

    union

    select avg(actual_thruput),avg(planned_thruput) from thruput

    union

    select avg(actual_spending),avg(planned_spending) from spending;

     

     

    First Inventory results

    Then Scrap

    Then FinishedGoods

    Then Thruput

    Then Spending

     

    Please help me.

     

    Thanks in advance.

    Thanks&Regards,

    Raj.

  • 9. Re: How to split one row into 2 columns
    ---Brodyaga--- Explorer
    Currently Being Moderated

    Hi,

     

     

    select actual_data, planned_data

    from (select 'a' || rownum rn, avg(actual_inventory) actual_data,avg(planned_inventory) planned_data from inventory

                union

          select 'b' || rownum, avg(actual_scrap),avg(planned_scrap) from scrap

             union

         select 'c' || rownum, avg(actual_finishedgoods),avg(planned_finishedgoods) from finishedgoods

             union

         select 'd' || rownum, avg(actual_thruput),avg(planned_thruput) from thruput

             union

         select 'e' || rownum, avg(actual_spending),avg(planned_spending) from spending);

  • 10. Re: How to split one row into 2 columns
    Mohan1312 Explorer
    Currently Being Moderated

    based on which column u wanna sort the data. show me the output you are gettting..

  • 11. Re: How to split one row into 2 columns
    Mohan1312 Explorer
    Currently Being Moderated

    try this

    select * from

    (select 1 Row_num,avg(actual_inventory) actual_data,avg(planned_inventory) planned_data from inventory

    union

    select 2,avg(actual_scrap),avg(planned_scrap) from scrap

    union

    select 3,avg(actual_finishedgoods),avg(planned_finishedgoods) from finishedgoods

    union

    select 4,avg(actual_thruput),avg(planned_thruput) from thruput

    union

    select 5,avg(actual_spending),avg(planned_spending) from spending)

    order by row_num;

  • 12. Re: How to split one row into 2 columns
    Raj_2013 Newbie
    Currently Being Moderated

    Hi,

     

    Thank you It is working.

     

     

     

    Thanks&Regards,

    Raj.

  • 13. Re: How to split one row into 2 columns
    Raj_2013 Newbie
    Currently Being Moderated

    Thank you All .

     

    For all your help me in resolving this .

     

     

    Thanks&Regards,

    Raj.