Sunday, 20 October 2013

SQL Query to Concatenate Multiple Column Values of a Table

SQL Server 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. To achieve the result as in the above SQL 2012  example we were needed to write a Sql Statement like below with CAST/CONVERT function:

CREATE TABLE CUSTOMERS ( CID INT PRIMARY KEY,CNAME VARCHAR(15) ,
 SEX 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.

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts