How to Concatenate multiple Column Values of a Table in SQL Server

SQL Query to Concatenate multiple Column Values of a Table

In previous versions of SQL Server where if we are using ‘+’ to concatenate the values then we were explicitly need to convert them to string and then concatenate.

CREATE TABLE CUSTOMERS ( CID INT PRIMARY KEY,CNAME VARCHAR(15) ,

 GENDER VARCHAR(3),SAL DECIMAL(10,2), UNIQUE(CNAME));

INSERT CUSTOMERS VALUES(1,'JAN','M',10000.15);

--Inserting Null Value in to a Column with UNIQUE Constraint.

INSERT CUSTOMERS VALUES(2,'','F',20000.25);

INSERT CUSTOMERS VALUES(3,NULL,'F',30000.35);

--Usage of CONVERT function to convert Decimal value to a String while Concatenation

SELECT CNAME AS CUSTOMER_NAME FROM CUSTOMERS;

SELECT 'The Customer'+' '+CNAME+' '+'Monthly Income is'+' '+CONVERT(VARCHAR(15),SAL) AS C_INCOME 
FROM CUSTOMERS;

--Usage of CAST function to convert Decimal value to a String while Concatenation

SELECT 'The Customer'+' '+CNAME+' '+'Monthly Income is'+' '+ 
CAST(SAL AS VARCHAR(15))AS C_INCOME 
FROM CUSTOMERS;

--Usage of ISNULL Function to convert Null Value to an Empty string while Concatenation

SELECT 'The Customer'+' '+ISNULL(CNAME,'')+' '+'Monthly Income is'+' '+ 
CAST(SAL AS VARCHAR(15)) AS C_INCOME 
FROM CUSTOMERS;

SELECT*FROM CUSTOMERS ;

DROP TABLE CUSTOMERS;

Notes : 
If any one of the value in concatenation is Null then whole concatenated string will become Null , to avoid this we have to convert the Null value to Empty string using  ISNULL Function.