Oracle sql create query to return value only for one 'employee'

user_12421734 0
Viewed 34

I need help how create query to get resault only for one emp. Emp is paramether for query.

CREATE table abc(
        abc_id number,
        year number,
        month number,
        emp_1 varchar2(100),
        hours_1 number,
        emp_2 varchar2(100),
        hours_2 number,
        emp_3 varchar2(100),
        hours_3 number,
        emp_4 varchar2(100),
        hours_4 number,
        emp_5 varchar2(100),
        hours_5 number
    )


Insert into abc (abc_id,year,month,emp_1,hours_1,emp_2,hours_2,emp_3,hours_3,emp_4,hours_4,emp_5,hours_5) values (1,2021,6,'PELE',8,'PELE',8,'PELE',8,'PELE',8,'PELE',8);
Insert into abc (abc_id,year,month,emp_1,hours_1,emp_2,hours_2,emp_3,hours_3,emp_4,hours_4,emp_5,hours_5) values (2,2021,6,'MESSI',8,'MESSI',8,'RONALDO',8,'RONALDO',8,'RONALDO',8);
Insert into abc (abc_id,year,month,emp_1,hours_1,emp_2,hours_2,emp_3,hours_3,emp_4,hours_4,emp_5,hours_5) values (3,2021,6,'RONALDO',8,'RONALDO',8,null,null,null,null,null,null);
Insert into abc (abc_id,year,month,emp_1,hours_1,emp_2,hours_2,emp_3,hours_3,emp_4,hours_4,emp_5,hours_5) values (4,2021,6,'PELE',8,'PELE',8,'MESSI',7,null,null,null,null);
commit;

How create query to get resault as:

enter image description here

Get all where emp is 'MESSI'.

oracle sql
1 Answers

This might be one option (:par_emp represents a parameter into which you'd enter employee's name):

select abc_id, year, month, :par_emp as emp, 
  case when emp_1 = :par_emp then hours_1 end hours_1,
  case when emp_2 = :par_emp then hours_2 end hours_2,
  case when emp_3 = :par_emp then hours_3 end hours_3,
  case when emp_4 = :par_emp then hours_4 end hours_4,
  case when emp_5 = :par_emp then hours_5 end hours_5
From abc 
where :par_emp in (emp_1, emp_2, emp_3, emp_4, emp_5)
order by abc_id;

Depending on tool you use, :par_emp might be something different. To illustrate it, I'm using SQL*Plus and substitution variable:

SQL> select abc_id, year, month, '&&par_emp' as emp,
  2    case when emp_1 = '&&par_emp' then hours_1 end hours_1,
  3    case when emp_2 = '&&par_emp' then hours_2 end hours_2,
  4    case when emp_3 = '&&par_emp' then hours_3 end hours_3,
  5    case when emp_4 = '&&par_emp' then hours_4 end hours_4,
  6    case when emp_5 = '&&par_emp' then hours_5 end hours_5
  7  From abc
  8  where '&&par_emp' in (emp_1, emp_2, emp_3, emp_4, emp_5)
  9  order by abc_id;

 ABC_ID  YEAR  MONTH EMP      HOURS_1    HOURS_2    HOURS_3    HOURS_4    HOURS_5
------- ----- ------ ----- ---------- ---------- ---------- ---------- ----------
      2  2021      6 MESSI          8          8
      4  2021      6 MESSI                                7

SQL>

P.S. Thank you for test case!

user_90979060
Related