I have come across a problem while doing
development. Finally I have sorted this problem. So I have decided to write an
blog on this. The problem and solution is as below:-
Problem
I have table as given below
![Image.jpg]()
Now I want to convert state row values to one string value.
Ex: Andhra Pradesh, Arunachal Pradesh, Assam,
Maharashtra
Solutions
For the above problem, below are different
queries to achieve this.
Query 1: Using "COALESCE" function
DECLARE
@temp VARCHAR(MAX)
SELECT
@temp =
COALESCE(@temp+',
' ,'')
+
state
FROM
[dbo].[country]
SELECT
@temp
Query 2: Using without "COALESCE" function
DECLARE
@temp VARCHAR(MAX)
SET
@temp =
''
SELECT
@temp =
@temp +
state +
', '
FROM
[dbo].[country]
SELECT
SUBSTRING(@temp,
0, LEN(@temp))
Query 3: Using "FOR XML PATH"
DECLARE
@temp VARCHAR(MAX)
SET
@temp =
(SELECT
', ' +
cast(s.state
as varchar)
FROM
[dbo].[country]
s
ORDER
BY s.state
FOR
XML PATH(''))
SELECT
SUBSTRING(@temp,
2, 200000)
AS
state
Hope this will help you to solve your problem.