Convert result in Json format in SQL Server 2014 [duplicate]

Question

I want to execute a select query on a table and I need this result in a JSON format. I want to do this using query only in SQL Server 2014.

My data is as follows.

DECLARE @Country TABLE (id INT, [CounrtyName] Varchar(30) )
INSERT INTO @Country VALUES (1,'India')
INSERT INTO @Country VALUES (1,'BAN')
INSERT INTO @Country VALUES (1,'USA')
INSERT INTO @Country VALUES (2,'Japan')
INSERT INTO @Country VALUES (2,'China')
INSERT INTO @Country VALUES (3,'Switzerland')
INSERT INTO @Country VALUES (4,'')

My result should be as below:

id  CounrtyName
1   {"India":"BAN":"USA"}
2   {"Japan":"China"}
3   {"Switzerland"}
4

Can anyone please suggest me the query for the above data.

Thanks

Answer 1

Your expected output looks nothing like JSON. Having said that, you could use the following to generated the expected result:

SELECT id, '{' + STUFF((
    SELECT ',"' + NULLIF(CountryName, '') + '"'
    FROM @country AS y
    WHERE y.id = x.id
    FOR XML PATH('')
), 1, 1, '') + '}'
FROM @country AS x
GROUP BY id