–=============================================
–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.