Simplest way of providing Comma seperated column values in SQL SERVER

Hello friends,
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,
StateName VARCHAR(500),
Cities VARCHAR(5000))

INSERT INTO @StateCities(StateId,StateName )
SELECT StateId,StateName
FROM State

UPDATE tmp
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.

Happy  Holidays 
Thanks & best regards,
Rajat Jaiswal

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s