-3

enter image description here

Display the Alternative columns from table row

user2432421
  • 1
  • 3
  • 2
    Sample data is better presented as [formatted text](https://meta.stackoverflow.com/a/251362). See [here](https://meta.stackexchange.com/questions/81852) for some tips on how to create nice looking tables. –  Oct 07 '19 at 11:04

2 Answers2

0

Convert the salary column to a string data type (so that salary and name have the same data type) and then UNPIVOT:

Oracle Setup:

CREATE TABLE test_data ( id, name, salary ) AS
 SELECT 100, 'A', 1000 FROM DUAL UNION ALL
 SELECT 101, 'B', 2000 FROM DUAL UNION ALL
 SELECT 102, 'C', 3000 FROM DUAL

Query:

SELECT id, value
FROM   (
  SELECT id, name, TO_CHAR( salary ) AS salary
  FROM   test_data
)
UNPIVOT ( value FOR key IN ( name, salary ) )

Output:

 ID | VALUE
--: | :----
100 | A    
100 | 1000 
101 | B    
101 | 2000 
102 | C    
102 | 3000

db<>fiddle here

MT0
  • 143,790
  • 11
  • 59
  • 117
0

Alternatively, UNION ALL can also work for you, If you agreed to change the datatype of Salary column - 

SELECT ID, Name "Name & Salary"
FROM TABLE1
UNION ALL
SELECT ID, Salary
FROM TABLE1
ORDER BY ID, Name
Ankit Bajpai
  • 13,128
  • 4
  • 25
  • 40