CODEWITHSUNDEEP
sqloracle-databaseselect
Varun Rao
Varun Rao

Reputation: 801

SQL to list Employee Name and Salary one below the other

A simple select statement on EMPLOYEES table gets this result.

select FIRST_NAME, SALARY from employees;

FIRST_NAME     SALARY
------------ --------
Steven          24000
Neena           17000
Lex             17000

But I need the output in this form.

COLUMN_TEXT
------------
Steven
24000
Neena
17000
Lex
17000

The employees FIRST_NAME needs to be in the first row followed by his/her SALARY in the next row.

I have tried the below. But is there a better way to do it?

select COLUMN_TEXT from (
select FIRST_NAME as COLUMN_TEXT
       , Row_Number() Over (Order By employee_ID) rn from employees
union all
select to_char(SALARY) as COLUMN_TEXT
      , Row_Number() Over (Order By employee_ID) rn from employees
order by rn
);

Upvotes: 1

Views: 77

Answers (2)

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

I would do it as you did. You could also use unpivot:

select column_text 
  from (select employee_id, first_name, to_char(salary) salary from employees) 
  unpivot (column_text for type in (first_name, salary))
  order by employee_id, type

SQLFiddle demo

unpivot requires Oracle 11g or later.

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

  1. CHR(10) => LF, line feed

try like below

select FIRST_NAME || chr(10) SALARY  from employees

Upvotes: 2

Related Questions