Home Oracle SQL Produce Multi-Columns From the Same Column With Mutual Rows Values

Questions

Oracle SQL Produce Multi-Columns From the Same Column With Mutual Rows Values

November 30, 2022

I have table element_types with column element_type containing amount types Basic Salary and Housing Allowance, i want to produce 2 column, one for Basic Salary and another for Housing Allowance, these types are linked to another table like employees and the values for those types ..etc, so I want to make 2 separate columns and not displaying types and amounts in rows.

Select ...,

(SELECT element_name FROM PAY_ELEMENT_TYPES_TL 
WHERE element_name IN ('Basic Salary')
) Salary


(SELECT element_name FROM PAY_ELEMENT_TYPES_TL 
WHERE element_name IN ('Housing Allowance')
) Housing

this gives error single-row subquery returns multiple rows, how can I achieve what I want?

i’ve tried to use multi-rows subquery using where but i want more than a column with different names derived from the same column

>Solution :

That’s a CASE WHEN use case:

SELECT 
CASE WHEN element_name = 'Basic Salary'
  THEN element_name END AS Salary,
CASE WHEN element_name = 'Housing Allowance'
  THEN element_name END AS Housing
FROM PAY_ELEMENT_TYPES_TL;

Try out: db<>fiddle