7 Replies Latest reply: Nov 11, 2013 6:17 AM by AlbertoFaenza Branched from an earlier discussion. RSS

    Re: Need help to construct a Query

    BluShadow

      937357 wrote:

       

      Hi Experts,

       

      Could you please helme to get required output.

      TEST

      Source Col 1 Col 2 Col 3 Col 4
      Row 1 r1*c1 r1*c2 r1*c3 r1*c4
      Row 2 r2*c1 r2*c2 r2*c3 r2*c4

       

      select 'r1 * c1' Col1,'r1 * c2' Col2,'r1 * c3' Col3, 'r1 * c4' Col4 from dual union all

      select 'r2 * c1' Col1 ,'r2 * c2' Col2,'r2 * c3' Col3,'r2 * c4'Col4 from dual

      Output:

       

      TEST

      Target Col 1 Col 2 Col 3 Col 4
      Row 1 r1 * c1 r2 * c1 r1 * c3 r2 * c3
      Row 2 r1 *c2 r2 * c2 r1 * c4 r2 * c4

       


      Thanks in Advance.

       

      Regards

      Cheers.

       

      Your question makes no sense.

       

      You output appears to show that col2 and col4 of row 1 and being swapped with col1 and col3 of row 2 respectively, yet your test data has no way of determining which row of data is "row 1" and which is "row 2" as there's nothing in the data to tell us that.

      You also haven't explained what the logic is for swapping these bits of data about, or what database version you are using (answers can vary greatly depending on which database version you are using).

       

      Read this:Re: 2. How do I ask a question on the forums?

       

      and post sufficient details for people to help you.

       

      We could easily come up with something like:

       

      SQL> ed
      Wrote file afiedt.buf

        1  with t as (select 1 as r, 'r1 * c1' Col1,'r1 * c2' Col2,'r1 * c3' Col3, 'r1 * c4' Col4 from dual union all
        2             select 2, 'r2 * c1' Col1 ,'r2 * c2' Col2,'r2 * c3' Col3,'r2 * c4'Col4 from dual
        3            )
        4  --
        5  -- end of test data
        6  --
        7  select l2 as r
        8        ,max(decode(rn,1,col)) as col1
        9        ,max(decode(rn,3,col)) as col2
      10        ,max(decode(rn,2,col)) as col3
      11        ,max(decode(rn,4,col)) as col4
      12  from (
      13        select mod(l+1,2)+1 as l2
      14              ,row_number() over (partition by mod(l+1,2)+1 order by ((r-1)*4)+l) as rn
      15              ,decode(l,1,col1,2,col2,3,col3,col4) as col
      16        from t cross join (select level l from dual connect by level <= 4)
      17       )
      18  group by l2
      19* order by 1
      SQL> /

       

               R COL1    COL2    COL3    COL4
      ---------- ------- ------- ------- -------
               1 r1 * c1 r2 * c1 r1 * c3 r2 * c3
               2 r1 * c2 r2 * c2 r1 * c4 r2 * c4

      But if we don't know exactly what you're after, this may or may not meet your requirements.

        • 1. Re: Need help to construct a Query
          937357

          Thanks you so much for reply and appologies for inconvenience:

           

          Please find the detai.

           

          Oracle Version:

          Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
          PL/SQL Release 10.2.0.4.0 - Production
          CORE 10.2.0.4.0 Production
          TNS for Solaris: Version 10.2.0.4.0 - Production
          NLSRTL Version 10.2.0.4.0 - Production

           

          I will be getting input as :

          TypeLoan for 102Loan for 105Rate for 102Rate for 105
          Cash1234
          Non Cash5678

           

          Need to modify below format.

           

          TypeLoan for CashLoan for Non CashRate of CashRate for Non Cash
          1021537
          1052648

           

          Please hlep me. Thanks....

           

          Regards

          Rghav

           

          Message was edited by: 937357

          • 2. Re: Need help to construct a Query
            AlbertoFaenza

            Hi,

             

            this looks like a PIVOT and UNPIVOT actions. Since you are working on 10g you don't have PIVOT and UNPIVOT but still you can have a look at:

            Re: 4. How do I convert rows to columns?

            which has some useful links.

             

            Regards.

            Alberto

            • 3. Re: Need help to construct a Query
              BluShadow

              Why have you branched my reply off from your original thread?  It's not possible for us moderators to re-merge the threads again, so you're just making a mess of the forums.

               

              DO NOT branch off replies from discussions.

              • 4. Re: Need help to construct a Query
                Mukesh75

                Try to solve your problem by useing pivot command.

                Example

                WITH   T
                AS
                (
                   SELECT
                      department_id
                   FROM
                      EMPloyees
                )
                SELECT
                   *
                FROM
                   T
                PIVOT
                (
                   COUNT(*)
                   FOR
                      (department_id)
                )

                • 5. Re: Need help to construct a Query
                  BluShadow

                  937357 wrote:

                   

                  Thanks you so much for reply and appologies for inconvenience:

                  Please find the detai.

                   

                  Oracle Version:

                  Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
                  PL/SQL Release 10.2.0.4.0 - Production
                  CORE 10.2.0.4.0 Production
                  TNS for Solaris: Version 10.2.0.4.0 - Production
                  NLSRTL Version 10.2.0.4.0 - Production

                   

                  I will be getting input as :

                  Type Loan for 102 Loan for 105 Rate for 102 Rate for 105
                  Cash 1 2 3 4
                  Non Cash 5 6 7 8

                   

                  Need to modify below format.

                   

                  Type Loan for Cash Loan for Non Cash Rate of Cash Rate for Non Cash
                  102 1 5 3 7
                  105 2 6 4 8

                   

                  Please hlep me. Thanks

                   

                  Regards

                  Rghav

                   

                  Where is that input coming from?

                  You are surely not storing data in a table with columns specifically for 102 and 105 etc. as columns should not relate to data in a properly designed relational database.

                  • 6. Re: Need help to construct a Query
                    BluShadow

                    Mukesh75 wrote:

                     

                    Try to solve your problem by useing pivot command.

                    Example

                    WITH   T
                    AS
                    (
                       SELECT
                          department_id
                       FROM
                          EMPloyees
                    )
                    SELECT
                       *
                    FROM
                       T
                    PIVOT
                    (
                       COUNT(*)
                       FOR
                          (department_id)
                    )

                     

                    Which, if you'd read, he won't be able to do as he doesn't have 11gR2.

                     

                    SQL> with t as (select 'Cash' as typ, 1 as l_102, 2 as l_105, 3 as r_102, 4 as r_105 from dual union all
                      2             select 'Non Cash', 5,6,7,8 from dual)
                      3  --
                      4  -- end of test data
                      5  --
                      6  select x as typ
                      7        ,max(decode(typ,'Cash',decode(x,102,l_102,l_105))) as l_cash
                      8        ,max(decode(typ,'Non Cash',decode(x,102,l_102,l_105))) as l_ncash
                      9        ,max(decode(typ,'Cash', decode(x,102,r_102,r_105))) as r_cash
                    10        ,max(decode(typ,'Non Cash', decode(x,102,r_102,r_105))) as r_ncash
                    11  from   t
                    12         cross join (select 102 as x from dual union all
                    13                     select 105 from dual)
                    14  group by x
                    15  order by x
                    16  /

                     

                           TYP     L_CASH    L_NCASH     R_CASH    R_NCASH
                    ---------- ---------- ---------- ---------- ----------
                           102          1          5          3          7
                           105          2          6          4          8

                    • 7. Re: Need help to construct a Query
                      AlbertoFaenza

                      Something like this:

                       

                      with mydata as

                      (

                         select 'Cash'     tp, 1 loan_102, 2 loan_105, 3 rate_102, 4 rate_105 from dual union all

                         select 'Non Cash' tp, 5 loan_102, 6 loan_105, 7 rate_102, 8 rate_105 from dual

                      )

                      , loans as

                      (

                        select 102 lntype from dual union all

                        select 105 lntype from dual

                      )

                      select lntype tp

                           , sum( case

                                    when lntype = 102 and tp='Cash'

                                    then loan_102

                                    when lntype = 105 and tp='Cash'

                                    then loan_105

                                  end

                                ) as loan_for_cash  

                           , sum( case

                                     when lntype = 102 and tp='Non Cash'

                                     then loan_102

                                     when lntype = 105 and tp='Non Cash'

                                     then loan_105

                                  end

                                ) as loan_for_non_cash  

                           , sum( case

                                     when lntype = 102 and tp='Cash'

                                     then rate_102

                                     when lntype = 105 and tp='Cash'

                                     then rate_105

                                  end

                                ) as rate_for_cash  

                           , sum( case

                                     when lntype = 102 and tp='Non Cash'

                                     then rate_102

                                     when lntype = 105 and tp='Non Cash'

                                     then rate_105

                                  end

                                ) as rate_for_non_cash  

                        from mydata, loans

                      group by lntype;

                       

                       

                              TP LOAN_FOR_CASH LOAN_FOR_NON_CASH RATE_FOR_CASH RATE_FOR_NON_CASH

                      ---------- ------------- ----------------- ------------- -----------------

                             102             1                 5             3                 7

                             105             2                 6             4                 8

                       

                      2 rows selected.

                       

                      Regards.

                      Alberto