13 Replies Latest reply: Aug 1, 2013 5:20 AM by Raj_2013 RSS

    How to split one row into 2 columns?

    Raj_2013

      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

          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

            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

              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

                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

                  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

                    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

                      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

                        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---

                          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

                            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

                              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

                                Hi,

                                 

                                Thank you It is working.

                                 

                                 

                                 

                                Thanks&Regards,

                                Raj.

                                • 13. Re: How to split one row into 2 columns
                                  Raj_2013

                                  Thank you All .

                                   

                                  For all your help me in resolving this .

                                   

                                   

                                  Thanks&Regards,

                                  Raj.