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

Re: Need help to construct a Query

BluShadow Guru Moderator
Currently Being Moderated

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 Newbie
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    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 Guru Moderator
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    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 Guru Moderator
    Currently Being Moderated

    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 Guru Moderator
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    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