Author: Akash Heranjal
One of my officemate Ranjan 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