SQL SERVER – Comma Separated Values (CSV) from Table Column different ways

Following are the different ways to get comma separated values from SQL Server database table column:

DECLARE @Wages  TABLE 

(

   EmpID   TINYINT IDENTITY(1,1),

   EmpName  VARCHAR(255)    ,

   HourlyWage   DECIMAL NULL,

   Salary   DECIMAL NULL,

   Commission   DECIMAL NULL,

   NumOfSales   TINYINT NULL

)

INSERT @Wages VALUES('Aaa',10.00, NULL, NULL, NULL)

INSERT @Wages VALUES('Bbb',20.00, NULL, NULL, NULL)

INSERT @Wages VALUES('Ccc',30.00, NULL, NULL, NULL)

INSERT @Wages VALUES('Ddd',40.00, NULL, NULL, NULL)

INSERT @Wages VALUES('Eee',NULL, 10000.00, NULL, NULL)

INSERT @Wages VALUES('Fff',NULL, 20000.00, NULL, NULL)

INSERT @Wages VALUES('Ggg',NULL, 30000.00, NULL, NULL)

INSERT @Wages VALUES('Hhh',NULL, 40000.00, NULL, NULL)

INSERT @Wages VALUES('Iii',NULL, NULL, 15000, 3)

INSERT @Wages VALUES('Jjj',NULL, NULL, 25000, 2)

INSERT @Wages VALUES('Kkk',NULL, NULL, 20000, 6)

INSERT @Wages VALUES('Lll',NULL, NULL, 14000, 4)

-- * --- Comma Separated Values using COALESCE

DECLARE @EmployeeList VARCHAR(2000)

SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') + EmpName

FROM @Wages

SELECT @EmployeeList AS EmployeeList

-- * --- Comma Separated Values using STUFF() AND XML PATH

SELECT STUFF( -- Remove first comma and a space

 (

     SELECT  ', ' + EmpName 

     FROM -- create comma separated values

   (

     SELECT EmpName FROM @Wages --Your query here

   ) AS T 

  FOR XML PATH('')

 )

 ,1,2,'') AS EmployeeList

-- * --- Comma Separated Values using SUBSTRINNG() AND XML PATH

SELECT SUBSTRING(

 ( SELECT (', ' + EmpName)

  FROM @Wages

  FOR XML PATH('')

 ) , 3,2000) 

AS EmployeeList

All above queries will give the same output:

Comma Separated Values using COALESCE() Comma Separated Values using COALESCE()

Comma Separated Values using STUFF() and XML PATH

Comma Separated Values using SUBSTRING() and XML PATH

By using any of the above method we can get comma separated values in sql server from database table column.

SQL SERVER – Handling Error & Transaction

Here is the simple way to handle error and transaction in SQL Server

BEGIN
SET NOCOUNT ON;

BEGIN TRY
BEGIN TRAN

--INSERT/UPDATE/DELETE statements here

COMMIT TRAN

END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRAN

DECLARE @errMsg VARCHAR(MAX)
SELECT @errMsg = ERROR_MESSAGE()
RAISERROR('Error : %s', 16, 1, @errMsg)

END CATCH
END

This can be helpful to manage transaction as well as error handling in SQL server. We can also keep a log of each error occurred during execution.