Convert SQL Server Table Data Into Comma Separated Values (CSV) Format

--=============================================
--Creating a table to store some sample records
--=============================================

CREATE TABLE [DBO].[MEmployee]
(
[EmpCode] INT IDENTITY (1,1) NOT NULL,
[EmpName] VARCHAR(100) NOT NULL,
[Active] BIT NOT NULL CONSTRAINT DF_MEmployee_Active DEFAULT 1, --1. ACTIVE 0. INACTIVE
CONSTRAINT [PK_MEmployee] PRIMARY KEY ([EmpCode]),
CONSTRAINT [CK_MEmployee_Active] CHECK ([Active] IN (0,1))
)
GO

--==================================================
--Inserting some test records into [MEmployee] Table
--==================================================

INSERT INTO [DBO].[MEmployee] ([EmpName],[Active]) VALUES ('Siva',1)
GO
INSERT INTO [DBO].[MEmployee] ([EmpName],[Active]) VALUES ('Parvathi',1)
GO
INSERT INTO [DBO].[MEmployee] ([EmpName],[Active]) VALUES ('Surya',1)
GO
INSERT INTO [DBO].[MEmployee] ([EmpName],[Active]) VALUES ('Satya',1)
GO
INSERT INTO [DBO].[MEmployee] ([EmpName],[Active]) VALUES ('Ajay',0)
GO

--======================================
--Selecting the records from [MEmployee]
--======================================

SELECT * FROM [DBO].[MEmployee]
GO

The output would be :











--====================================================
--Generating the records in the Comma Separated Format
--====================================================

DECLARE @Result VARCHAR(MAX)
--==============================================
--To display the field heading in the first row
--==============================================
SET @Result = 'EmpCode,EmpName,Active'
SELECT
@Result = COALESCE(@Result + '|','') + CONVERT(VARCHAR(10),[EmpCode]) +','+[EmpName]+','+CONVERT(VARCHAR(2),[Active])
FROM
[DBO].[MEmployee]

PRINT @Result

See the result as shown below:





Note :
1. COALESCE() returns the first NOTNULL expression among its arguments.
2. "|" is used for row delimiter.