Query to get output in a particular format (merged 3) [message #446422] |
Mon, 08 March 2010 05:22  |
mathewpsimon
Messages: 19 Registered: March 2010
|
Junior Member |
|
|
I have attached an SQL script.I would like to have the output in the fashion given below -
PERIOD_START_DATE PERIOD_END_DATE EMPLOYEE_NUMBER FULL_NAME Loan Amount Loan Type Pay Value Outstanding Amount
1-Jul-09 31-Jul-09 24200 Jonathan 4800 004 800 4000
1-Jul-09 31-Jul-09 24200 Jonathan 3260 002 270 2990
the one in bold and underlined is the Column heading.Please help to get output as above.
-
Attachment: aa.sql
(Size: 7.32KB, Downloaded 817 times)
[Updated on: Mon, 08 March 2010 05:42] by Moderator Report message to a moderator
|
|
|
|
|
|
Re: Query to get output in a particular format [message #446432 is a reply to message #446430] |
Mon, 08 March 2010 05:54   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Here's an idea of what you can do (once you will fix your data and model, it seems you mix up type and amount, a very bad idea like storing numbers in varchar2):
SQL> with lines as (select level line from dual connect by level <= 4)
2 select period_start_date, period_end_date, employee_number,
3 substr(full_name,1,instr(full_name,' ')-1) full_name,
4 sum(decode(line,1,decode(instr(lower(in_val_name),'loan'),0,0,result_value)))
5 "Loan Amount",
6 sum(decode(line,2,decode(lower(in_val_name),'pay value',result_value)))
7 "Pay Value",
8 sum(decode(line,3,decode(lower(in_val_name),'outstanding amount',result_value)))
9 "Outstanding Amount"
10 from xx, lines
11 group by period_start_date, period_end_date, employee_number, full_name
12 order by period_start_date, period_end_date, employee_number
13 /
PERIOD_STA PERIOD_END EMPLOYEE_N FULL_NAME Loan Amount Pay Value Outstanding Amount
---------- ---------- ---------- ------------ ----------- ---------- ------------------
01/01/2010 31/01/2010 24200 Jonathan 8078 1070 2710
01/02/2010 28/02/2010 24200 Jonathan 8078 1070 1640
2 rows selected.
Regards
Michel
|
|
|
Re: Query to get output in a particular format [message #446433 is a reply to message #446426] |
Mon, 08 March 2010 05:54  |
mathewpsimon
Messages: 19 Registered: March 2010
|
Junior Member |
|
|
Michel,
Answer -1
Each Employee who has take loan will have each of these values.
1)Loan Amount 2)Loan Type 3)Pay Value 4)Outstanding Amount.It can be even hardcoded also like if the IN_VALUE_NAME=Loan Amount then the RESULT_VALUE can be shown under that.There a key which links Loan Amount to the same Loan Type,Pay Value etc.
Answer -2
Since it is as rows there are many but if these 4 values become column then it becomes 2.
Answer -3
Yes,the period is a mistake in the shown output.It should be Jan 10
|
|
|