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

Question

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

Does this answer your question? [Oracle SQL pivot query](https://stackoverflow.com/questions/4841718/oracle-sql-pivot-query) — astentx, Nov 30 '22 at 07:24

Jonas Metzler · Accepted Answer · 2022-11-30T08:28:41.193

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)

edited Nov 30 '22 at 08:28

answered Nov 30 '22 at 07:19

Jonas Metzler

4,517
1
5
17

it says ```too many values``` – Faris Adnan Nov 30 '22 at 07:28

score 0 · Answer 2 · answered Nov 30 '22 at 08:11

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

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

2 Answers2