I have table employees with column emp_id and emp_name, table elements containing column person_id and element_name such as Basic Salary, and table values containing column value_result such as 1500 and also contains rubbish data that is not wanted, in order to get rid of it I need to use table values_type containing column value_type,

select element_name , // e.g. Basic Salary
values // e.g. 1500,
emp_name // john


from values,values_type, elements,employees

join elements on elements.emp_id= employees.emp_id
and element_name IN ('Basic Salary', 'Transportation Allowance')
join values on values.element_id = elements.element_id
join values_type on values_type.value_id = values.value_id
and values_type.value_type = 'Amount` // in order to give me desirable values in numbers

this works fine, but it will give me 2 rows for each employee, one for Basic Salary and one for Transportation Allowance, what I want is to make 2 columns from element_name rows, those are Basic Salary and Transportation Allowance, then I want to bring the related rows (values) from table values under each column based on the condition types.value_type = 'Amount` from table values_type, how can I achieve that ?

sample result

emp_id     element_name                values   
1          Basic Salary                1500
1          Transportation Allowance    200      

wanted results

emp_id     Basic Salary                Transportation Allowance
1          1500                        200

i've tried to join table values 2 times in order to use different conditions on columns :

select element_name , // e.g. Basic Salary
v1.values, // e.g. 1500 ( basic salary )
v2.values, // e.g. 200 ( transportation allowance )
emp_name, // john


from values,values_type, elements,employees

join elements on elements.emp_id= employees.emp_id
and element_name IN ('Basic Salary', 'Transportation Allowance')

left outer join values v1 on v1.values.element_id = elements.element_id
left outer join values v2 on v2.values.element_id = elements.element_id

join values_type on values_type.value_id = values.value_id
and values_type.value_type = 'Amount` // in order to give me desirable values in numbers

but then i need to join values_type to each one:

join values_type t1 on t1.values_type.value_id = v1.values.value_id
and t1.value_type = 'Amount` // in order to give me desirable values in numbers

join values_type t2 on t2.values_type.value_id = v2.values.value_id
and t2.value_type = 'Amount` // in order to give me desirable values in numbers

which will of course not work, it will give empty results, I've been stuck at this for 2 days now..

CodePudding user response：

May be something like this one

select emp_id, // e.g. Basic Salary
sum(decode(element_name,'Basic Salary',values,0)) Basic_Salary, 
sum(decode(element_name,'Transportatiion Allowance',values,0)) Transportation_Allowance


from values,values_type, elements,employees

join elements on elements.emp_id= employees.emp_id
and element_name IN ('Basic Salary', 'Transportation Allowance')
join values on values.element_id = elements.element_id
join values_type on values_type.value_id = values.value_id
and values_type.value_type = 'Amount' 
group by emp_id