Home » SQL & PL/SQL » SQL & PL/SQL » Query to get output in a particular format (merged 3) (Oracle 10g)
Query to get output in a particular format (merged 3) [message #446422] Mon, 08 March 2010 05:22 Go to next message
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 138 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 #446426 is a reply to message #446422] Mon, 08 March 2010 05:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And what is the definition (formula) of the added columns?
And why there are 2 lines in your output?
And in your test case, the dates are in 01/2010 and 02/2010, not July, please post the result for your data.

Regards
Michel

[Updated on: Mon, 08 March 2010 05:36]

Report message to a moderator

Re: Query to get output in a particular format [message #446427 is a reply to message #446422] Mon, 08 March 2010 05:37 Go to previous messageGo to next message
mathewpsimon
Messages: 19
Registered: March 2010
Junior Member
I am not sure I understood your question.But from what I understand these columns are as values in the row of one of the columns.
Re: Query to get output in a particular format [message #446430 is a reply to message #446427] Mon, 08 March 2010 05:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What are the formula to get the values in the following columns:
Loan Amount Loan Type Pay Value Outstanding Amount

For instance where does "loan type" come from?

Note that I added new questions in my previous post.

Regards
Michel

[Updated on: Mon, 08 March 2010 05:46]

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 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous message
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
Previous Topic: cursor with parameter inside function
Next Topic: Convertion of Julian Date into dd/mm/yyyy
Goto Forum:
  


Current Time: Mon Dec 05 21:22:26 CST 2016

Total time taken to generate the page: 0.13428 seconds