SQL Query to give output in one row

Question

I have created the below query -

select person_number
,sal.current_salary
,(CASE WHEN '2021-03-31' between to_char(sal.date_From ,'YYYY-MM-DD')
 AND to_char(sal.date_to ,'YYYY-MM-DD')
            THEN SALARY_AMOUNT
            else 
            null 
             END) "2019_sal"
,(CASE WHEN '2020-03-31' between to_char(sal.date_From ,'YYYY-MM-DD')
 AND to_char(sal.date_to ,'YYYY-MM-DD')
            THEN SALARY_AMOUNT
            else 
            null 
             END) "2020_sal"
,(CASE WHEN '2019-03-31' between to_char(sal.date_From ,'YYYY-MM-DD')
 AND to_char(sal.date_to ,'YYYY-MM-DD')
            THEN SALARY_AMOUNT
            else 
            null 
             END) "2019_sal"
from per_all_people_f papf,
comp_salary sal
where papf.person_id = sal.person_id 
and papf.person_number = :p_emp_number
and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date

This gives me output like -

Person_number Current_salary                    2019_sal            2020_sal    
10              292929                                      
10                                              287226                  
10                                                                  300000

11              282726                          
11                                                                  278090

Is there anyway i can tweak the above query to give me the output like -

Person_number Current_salary                    2019_sal            2020_sal    
10              292929                          287226              300000                                                              
11              282726                                              278090

I.e. get the data against and employee in a single row. Can I use any function for this ?

use MAX(fieldname) around your 3 additional columns and then group by person_number. — xQbert
– xQbert, Commented Jan 19, 2022 at 18:32

xQbert · Accepted Answer · 2022-01-19 18:52:39Z

Use simple aggregation to get the "MAX" value in each column for each person then group by that person. This will collapse each value into 1 record for each person number. It does however assume there will always only be 1 value for each column for a given person_number. Based on sample data; that appears to be the case.

select person_number
,max(sal.current_salary) "current_Salary"
,max(CASE WHEN '2021-03-31' between to_char(sal.date_From ,'YYYY-MM-DD')
 AND to_char(sal.date_to ,'YYYY-MM-DD')
            THEN SALARY_AMOUNT
            else 
            null 
             END) "2019_sal"
,max(CASE WHEN '2020-03-31' between to_char(sal.date_From ,'YYYY-MM-DD')
 AND to_char(sal.date_to ,'YYYY-MM-DD')
            THEN SALARY_AMOUNT
            else 
            null 
             END) "2020_sal"
,max(CASE WHEN '2019-03-31' between to_char(sal.date_From ,'YYYY-MM-DD')
 AND to_char(sal.date_to ,'YYYY-MM-DD')
            THEN SALARY_AMOUNT
            else 
            null 
             END) "2019_sal"
from per_all_people_f papf,
comp_salary sal
where papf.person_id = sal.person_id 
and papf.person_number = :p_emp_number
and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
GROUP BY person_number

Collectives™ on Stack Overflow

SQL Query to give output in one row

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related