Home » SQL & PL/SQL » SQL & PL/SQL » Retrieve data from rows into columns
Retrieve data from rows into columns [message #396308] Mon, 06 April 2009 01:55 Go to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Hi,

Im trying to convert data into columns. As below
create table test (
  account number,
  currency varchar2(3),
  flag varchar2(1), -- E for expense; A for allocation
  amount number,
  t_flag varchar2(1)) -- set to Y if values is translated to MYR
  
  
insert into test VALUES(1,'USD','E',20,NULL);
insert into test VALUES(1,'MYR','E',80,'Y');
insert into test VALUES(1,'USD','A',30,NULL);
insert into test VALUES(2,'MYR','E',40,'Y');
insert into test VALUES(2,'USD','E',10,NULL);
insert into test VALUES(3,'USD','A',40,NULL);

COMMIT;


I need to generate the output shown by this query

select  a.account, a.currency, a.amount allocation, b.expense, b.converted_currency, b.converted_expense from test a,
(
select a.account, a.currency, a.amount expense, b.currency converted_currency, b.amount converted_expense from
   (select account, currency, amount from test where t_flag is null and flag = 'E' ) a,
   (select account, currency, amount from test where t_flag is not null and flag = 'E' ) b
where a.account = b.account
) b
 where a.account = b.account(+) and
 a.flag = 'A'

union

select a.account, a.currency, null allocation , a.amount expense, b.currency converted_currency, b.amount converted_expense from
   (select account, currency, amount from test where t_flag is null and flag = 'E' ) a,
   (select account, currency, amount from test where t_flag is not null and flag = 'E' ) b
where a.account = b.account

and not exists
   (select 'x' from test where flag='A' and account = a.account)



Is there any other way i can achieve this. This query is hitting the main table many times, just thought of rewriting this in a better way.

Thanks in advance for any suggestion

[Updated on: Mon, 06 April 2009 01:59]

Report message to a moderator

Re: Retrieve data from rows into columns [message #396316 is a reply to message #396308] Mon, 06 April 2009 02:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From you test case, what result do you want?
Also explain with words not with a query, are you expecting we reverse engineer your code to know what you are trying to do?

Also, keep your code/query lines in 80 characters, use a formatter to pretty print it.

Regards
Michel


Re: Retrieve data from rows into columns [message #396321 is a reply to message #396316] Mon, 06 April 2009 02:15 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Hi,

Appologies for that.

Actually im trying to achieve the following.

I want to display the above data produced in test case in 1 row for each 'account'

The logic is quite straight forward, lets say i have following data (for account: 1)


insert into test VALUES(1,'USD','E',20,NULL);
insert into test VALUES(1,'MYR','E',80,'Y');
insert into test VALUES(1,'USD','A',30,NULL);


For account: 1, what it means is there was allocation in USD=30, then the expense was: 20, and then the expense was converted to MYR: 80

So i wanted to display the records something like this
Account|Currency|Allocation|Expense|ConvertedCurrency|ConvertedExpense

1|USD|30|20|MYR|80


Re: Retrieve data from rows into columns [message #396333 is a reply to message #396321] Mon, 06 April 2009 02:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I'm not sure I fully understand your requirements but you can do something like the following
(which assume you have only one row per account and displayed column):
SQL> select account,
  2         max(decode(t_flag,null,currency)) currency,
  3         max(decode(flag,'A',amount)) allocation,
  4         max(case when flag='E' and t_flag is null then amount end) expense,
  5         max(decode(t_flag,'Y',currency)) convertedcurrency,
  6         max(case when flag='E' and t_flag='Y' then amount end) convertedexpense
  7  from test
  8  group by account;
   ACCOUNT CUR ALLOCATION    EXPENSE CON CONVERTEDEXPENSE
---------- --- ---------- ---------- --- ----------------
         1 USD         30         20 MYR               80
         2 USD                    10 MYR               40
         3 USD         40

3 rows selected.

Regards
Michel

[Updated on: Mon, 06 April 2009 02:50]

Report message to a moderator

Re: Retrieve data from rows into columns [message #396335 is a reply to message #396333] Mon, 06 April 2009 03:00 Go to previous message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Thats a very good example, appreciate your kindness in showing this solution. I will try it out. Thank You.
Previous Topic: pdf (merged)
Next Topic: Date Function
Goto Forum:
  


Current Time: Sat Dec 03 13:46:09 CST 2016

Total time taken to generate the page: 0.09886 seconds