SQL - Concatenating Column Values

Problem

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

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

However, the data that you need to generate this result set comes from two different columns, the ENAME and JOB columns in the EMP table:

 
 select ename, job
   from emp
   where deptno = 10

  ENAME      JOB
  ---------- ---------
  CLARK      MANAGER
  KING       PRESIDENT
  MILLER     CLERK

Solution

Find and 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:

 1 select ename||' WORKS AS A '||job as msg
 2   from emp
 3  where deptno=10

MySQL

This database supports a function called CONCAT:

 1 select concat(ename, ' WORKS AS A ',job) as msg
 2   from
 3  where deptno=10

SQL Server

Use the "+" operator for concatenation:

 1 select ename + ' WORKS AS A ' + job as msg
 2   from emp
 3  where deptno=10

Discussion

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.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table