How to concatenate column values in SQL?

To return values in multiple columns as one column. For example, you
would like to produce this result set from a query against the Staff table:

CLARK WORKS AS A MANAGER
KING WORKS AS A PRESIDENT
MILLER WORKS AS A CLERK

Staff Table

empno ename job mgr_no hiredate sal commission deptno
7369 ALI CLERK 7902 1980-12-17 800.00 NULL 20
7499 ALLEN SALESMAN 7698 1981-02-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-02-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-04-02 2975.00 NULL 20
7654 MARTIN SALESMAN 7698 1981-09-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-05-01 2850.00 NULL 30
7782 CLARK MANAGER 7839 1981-06-09 2450.00 NULL 10
7788 RAHUL ANALYST 7566 1982-12-09 3000.00 NULL 20
7839 RAJ PRESIDENT NULL 1981-11-17 5000.00 NULL 10
7844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 30
7876 ADAM CLERK 7788 1983-01-12 1100.00 NULL 20
7900 JAMES CLERK 7698 1981-12-03 950.00 NULL 30
7902 ARHAM ANALYST 7566 1981-12-03 3000.00 NULL 20
7934 MILLER CLERK 7782 1982-01-23 1300.00 NULL 10

However, the data that you need to generate this result set comes from two different
columns, the ename and job columns in the Staff table.

Example

use the built-in function provided by your DBMS to concatenate values
from multiple columns.

DB2, Oracle, PostgreSQL

These databases use the double vertical bar as the concatenation operator:

SELECT ename||' WORKS AS A '||job as msg
FROM Staff
WHERE deptno=10

Output

msg
CLARK WORKS AS A MANAGER
RAJ WORKS AS A PRESIDENT
MILLER WORKS AS A CLERK

MySQL

This database supports a function called CONCAT:

SELECT concat(ename, ' WORKS AS A ',job) as msg
FROM Staff
WHERE deptno=10

Output

msg
CLARK WORKS AS A MANAGER
RAJ WORKS AS A PRESIDENT
MILLER WORKS AS A CLERK

SQL Server

Use the + operator for concatenation:

SELECT ename + ' WORKS AS A ' + job as msg
FROM Staff
WHERE deptno=10

Output

msg
CLARK WORKS AS A MANAGER
RAJ WORKS AS A PRESIDENT
MILLER WORKS AS A CLERK

Use the CONCAT function to concatenate values from multiple columns. The || is a
shortcut for the CONCAT function in DB2, Oracle, and PostgreSQL, while + is the
shortcut for SQL Server.