Display multiple rows as 1 1

[danmul]

Hi,

I have a query that returns the last 3 salaries of employees but I want to display the results in 1 row as Sal1, Sal2, Sal3. Anybody know how to do this?
My query is:
select b.REFNO, s.SA02D, s.SA04P from basic b, salary_history s
where b.BGROUP = s.BGROUP
and b.REFNO = s.REFNO
and s.SA02D in (select sh1.sa02d from (select sh.SA02D, refno
from salary_history sh
order by sh.SA02D desc) sh1
where rownum <=3
and sh1.refno = b.refno)
and b.SD01X = '8025'
order by b.refno, s.SA02D desc;

Cheers,
Daniel.

 

[LKBrwnDBA]

This question has been answered multiple times, did you even bother to search the forum for "rows to columns" or "crosstab query"?


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LKBrwnDBA]

Also "pivot table"



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

Daniel,

Your specifications are rather sparse. To resolve your question, I made the following presumptions:[ul][li]For the purpose of organising the salaries to determine "last 3 salaries", I presumed that "s.SA02D" is the DATE that employees began their salary for that row.[/li][li]"s.sa04P" = Salary amount.[/li][li]"REFNO" = Employee number.[/li][li]"Sal1", "Sal2", "Sal3" are in "Newest-to-Oldest" order.[/li][/ul]

If these presumptions are satisfactory, then here is a query that produces the results you specify, from the sample that that I have concocted:

(Sample data)
SQL> select * from basic;

     REFNO     BGROUP      SD01X
---------- ---------- ----------
         1         10       8025
         2         10       8026
         3         10       8025

3 rows selected.

SQL> select * from salary_history;

     REFNO SA02D          SA04P     BGROUP
---------- --------- ---------- ----------
         1 24-MAY-03      50000         10
         1 23-MAY-04      55000         10
         1 23-MAY-05      61000         10
         1 23-MAY-06      67500         10
         1 23-MAY-07      73000         10
         2 24-MAY-03      50000         10
         2 23-MAY-04      55000         10
         2 23-MAY-05      61000         10
         2 23-MAY-06      67500         10
         2 23-MAY-07      73000         10
         3 24-MAY-03      60000         10
         3 23-MAY-04      65000         10
         3 23-MAY-05      71000         10
         3 23-MAY-06      77500         10
         3 23-MAY-07      83000         10

15 rows selected.

(Sample query)
select refno
      ,max(decode(rnk,1,pay,0)) Sal1
      ,max(decode(rnk,2,pay,0)) Sal2
      ,max(decode(rnk,3,pay,0)) Sal3
  from (select refno, sal_date, pay, rank() over (partition by refno order by sal_date desc) rnk
          from (select b.refno, s.sa02d sal_date, s.sa04P pay
                  from basic b, salary_history s
                 where b.bgroup = s.bgroup
                   and b.refno = s.refno
                   and b.sd01x = '8025'
               )
       )
 group by refno
 order by refno
/

     REFNO       SAL1       SAL2       SAL3
---------- ---------- ---------- ----------
         1      73000      67500      61000
         3      83000      77500      71000

2 rows selected.

The above code uses some rather sophisticated features of Oracle, including in-line Views and Oracle's Analytics [e.g., "rank() over (partition...")]

Let us know what questions you have after you have studied the code and the results, and researched Oracle Analytics (suggestion: Analytic functions by Example is excellent.)

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[danmul]

Hi Dave,

This is great. I have read the analytic functions and this is going to be make my life so much easier. I have to support report requests by the business on a daily business and this can be applied in a lot of the cases. I have never came across "partition by" before. I should have logged this request a long time ago!
Just 1 question with code you gave me. This retrieves all rows and then displays the 1st 3 salaries. I had used where rownum <=3. Is there an impact on speed to use rownum in this case or not.
Thanks to LKBrwnDBA also. I had done a few searches but was not sure what exactly to be searching for.

Cheers,
Daniel.

 

[SantaMufasa]

Dan,

ROWNUM is a very inexpensive function since Oracle automatically genererates a ROWNUM value for every incoming row, whether you refer to ROWNUM or not.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[danmul]

Thanks,
Sorry about replying so late. I've been away on a long weekend with the family.

Daniel.