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

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

Contents hide

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

Problem Description:

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 – 1

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

Use aggregation around if required (MAX, MIN etc., depending on what you need)

Solution – 2

You might use a conditional aggregation to pivot the data as desired like

SELECT MAX(CASE
           WHEN element_name = 'Basic Salary' THEN
                element_name
            END) AS Salary,
       MAX(CASE
           WHEN element_name = 'Housing Allowance' THEN
                element_name
            END) AS Housing
  FROM pay_element_types_tl

eg. move conditions from the WHERE clause to CASE..WHEN expressions while aggregating the values to prevent generating multiple rows

Rate this post