oracle

Wednesday, 19 April 2017

SQL:How to Combine result of Two or more columns of a table.

For example if we want to combine the data of ename and job coloumn in a single column, We use Concatenation operator or concatenation function .

Syntax:

Select col_1 || col_2 from table_name;

Example:

select ename || job from emp;

Result:
Ename || job
SMITHCLERK
ALLENSALESMAN
WARDSALESMAN
JONESMANAGER
MARTINSALESMAN
BLAKEMANAGER
CLARKMANAGER
SCOTTANALYST
KINGPRESIDENT
TURNERSALESMAN
ADAMSCLERK
JAMESCLERK
FORDANALYST
MILLERCLERK

Here Problem is that there is no space between the data of two coloumns.
We use space ' ' in the single quotes with the concatenation operator to overcome on the above problem.

Query:
select ename ||' '|| job from emp;

Result:
ename ||' '||job
SMITH CLERK
ALLEN SALESMAN
WARD SALESMAN
JONES MANAGER
MARTIN SALESMAN
BLAKE MANAGER
CLARK MANAGER
SCOTT ANALYST
KING PRESIDENT
TURNER SALESMAN
ADAMS CLERK
JAMES CLERK
FORD ANALYST

MILLER CLERK

Here the above problem is solved.

We can further clear our concepts by the below example.

Question:
Write query which give the result as, The Salary of Mr. Smith is 800.

Asnwer:
select 'The salary of Mr. '|| ename ||' is '|| sal from emp;

Result:
'The salary of Mr. '|| ename ||' is '|| sal
The salary of Mr. SMITH is 800
The salary of Mr. ALLEN is 1600
The salary of Mr. WARD is 1250
The salary of Mr. JONES is 2975
The salary of Mr. MARTIN is 1250
The salary of Mr. BLAKE is 2850
The salary of Mr. CLARK is 2450
The salary of Mr. SCOTT is 3000
The salary of Mr. KING is 5000
The salary of Mr. TURNER is 1500
The salary of Mr. ADAMS is 1100
The salary of Mr. JAMES is 950
The salary of Mr. FORD is 3000

The salary of Mr. MILLER is 1300

Here is also a problem with the coloumn name . we can remove this problem by this.
Query:
select 'The salary of Mr. '|| ename ||' is '|| sal as Employees_Detail from emp;

Reuslt:
Employees_Detail
The salary of Mr. SMITH is 800
The salary of Mr. ALLEN is 1600
The salary of Mr. WARD is 1250
The salary of Mr. JONES is 2975
The salary of Mr. MARTIN is 1250
The salary of Mr. BLAKE is 2850
The salary of Mr. CLARK is 2450
The salary of Mr. SCOTT is 3000
The salary of Mr. KING is 5000
The salary of Mr. TURNER is 1500
The salary of Mr. ADAMS is 1100
The salary of Mr. JAMES is 950
The salary of Mr. FORD is 3000
The salary of Mr. MILLER is 1300

Thanks everyone
Hope it will be helpfull for you.

No comments:

Post a Comment