One of my officemate Ranjan Patra did some research on internet and found a simple and quick solution to have row data as comma separated string. The requirement of the task is as follows

Example Requirement Case:

There is a table by name tbl_CityState with three columns, RecId which is an Identity Column, StateName which is VarChar(50) holding the name of the state or province, and finally the CityName which is VarChar(50) holding the name of the cities with-in a State or Province. The script for creating the table is as below

CREATE TABLE [dbo].[tbl_CityState](
[RecID] [int] IDENTITY(1,1) NOT NULL,
[StateName] [varchar](50) NOT NULL,
[CityName] [varchar](50) NOT NULL,
CONSTRAINT [PK_tbl_CityState] PRIMARY KEY CLUSTERED
(
[RecID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

To Insert data in the table, run the below scripts

Insert into tbl_CityState (StateName, CityName) Values('Karnataka', 'Bangalore')
Insert into tbl_CityState (StateName, CityName) Values('Karnataka', 'Mangalore')
Insert into tbl_CityState (StateName, CityName) Values('Karnataka', 'Hubli')
Insert into tbl_CityState (StateName, CityName) Values('Karnataka', 'Mysore')
Insert into tbl_CityState (StateName, CityName) Values('Goa', 'Madgaon')
Insert into tbl_CityState (StateName, CityName) Values('Goa', 'Panaji')
Insert into tbl_CityState (StateName, CityName) Values('Maharastra', 'Mumbai')
Insert into tbl_CityState (StateName, CityName) Values('Maharastra', 'Pune')

The table looks like this…

The task is to get the name of the cities separated by comma against each distinct state


Solution:

Create a User Defined Function, for example func_CityName

CREATE FUNCTION [dbo].[func_CityName]
(
@StateName VarChar(100)
)
RETURNS Varchar(7000)
AS
BEGIN

DECLARE @result AS VarChar(4000)
SELECT @result = (coalesce(@result + ', ' , '' ) + B.CityName)
FROM tbl_CityState AS B WHERE B.StateName=@StateName

RETURN @result

END

The function above takes the name of the state as input, creates a string of all cities where the state name is as that of the input parameter and finally returns the concatenated comma separated string of city names. The key logic is the effective usage of COALESCE to build string.

SELECT @result = (COALESCE(@result + ', ' , '') + B.CityName)
FROM tbl_CityState AS B WHERE B.StateName=@StateName