Home » SQL & PL/SQL » SQL & PL/SQL » Tough Query.
Tough Query. [message #194937] Tue, 26 September 2006 04:47 Go to next message
ajithr
Messages: 1
Registered: September 2006
Junior Member
Hi all,

I have a table with columns

ename
sal_month
sal_year
salary

i want an output like this

year 2005

ename jan feb mar apr may jun jul aug sep oct nov dec
Ajith 100 100

nirmal 90 120

year 2006

ename jan feb mar apr may jun jul aug sep oct nov dec
Ajith 100 100

nirmal 90 120

for each and every month the salary should be displayed below the month label.

This was asked to me in an interview.. The interviewer told me that one sql query is enough..he told there is no need to use pl / sql....can u guys plz help me in this..
Re: Tough Query. [message #194948 is a reply to message #194937] Tue, 26 September 2006 05:13 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
What have you tried so far?

Re: Tough Query. [message #194951 is a reply to message #194948] Tue, 26 September 2006 05:17 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, they've tried asking here.
Laughing
Re: Tough Query. [message #194955 is a reply to message #194951] Tue, 26 September 2006 05:26 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Laughing
Re: Tough Query. [message #195782 is a reply to message #194937] Mon, 02 October 2006 06:31 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Try following:

SELECT emplist.ename, emplist.sal_year,
t1.salary jan,
t2.salary feb,
...
t12.salary dec
FROM
(SELECT DISTINCT ename, sal_year FROM my_table) emplist,
my_table t1,
my_table t2,
...
my_table t12
WHERE
emplist.ename = t1.ename(+) and
emplist.sal_year = t1.sal_year(+) and
t1.sal_month(+) = 1 AND
emplist.ename = t2.ename(+) and
emplist.sal_year = t2.sal_year(+) and
t2.sal_month(+) = 2 AND
...
emplist.ename = t12.ename(+) and
emplist.sal_year = t12.sal_year(+) and
t12.sal_month(+) = 12

However I don't garantee performance.

HTH
Previous Topic: query taking long time
Next Topic: ORA-02067 & ORA-00604
Goto Forum:
  


Current Time: Mon Dec 05 19:17:45 CST 2016

Total time taken to generate the page: 0.11292 seconds