Most of the time we require comma separated value of a column according to some relation.
The simplest way of this to create a user define function and create a comma separated value according to their respective other relation value.
Just understand with below example.
Suppose we have a normalize database design in which we have two table
1. State: – State Master is table which contains State of India. And it has following columns
a) StateId (Auto incremental value)
b) Name (State Name)
2. City: – Which contain cities of States according to their respective relation. It has following columns
a) CityId ( Auto incremental Id)
b) StateId (Reference of State table Id
c) City name
Please take a look on below fig with some value of city and state
Now suppose we require following result
Then we are going to use here a simple operator which is “FOR XML Path(‘’)” option so below is use of XML path.
Please run the below query for desire result.
DECLARE @StateCities AS TABLE (StateId INT,
INSERT INTO @StateCities(StateId,StateName )
SET Cities = (SELECT c.CityName + ‘,’
FROM dbo.City c
WHERE c.StateId = tmp.StateId
ORDER BY c.CityName
FOR XML PATH (”))
FROM @StateCities tmp
SELECT * FROM @StateCities WHERE Cities IS NOT NULL
I hope it would help you.
Thanks & best regards,