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.buf1 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.
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
Message was edited by: 937357
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
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.
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)
)
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.
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
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