0

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

Bhushan
  • 114
  • 12
  • What have you tried so far? `FOR JSON` isn't available in SQL Server 2014 (though as Martin mentions, this isn't valid JSON data), but at the end of the day, this is just a Colon (`:`) delimited string wrapped in braces (`{}`); and both `STUFF` and `FOR XML PATH` *are* available in SQL Server 2014. – Thom A Mar 03 '20 at 11:34
  • Does this answer your question? [Comma separated results in SQL](https://stackoverflow.com/q/18870326/2029983) – Thom A Mar 03 '20 at 11:34
  • 1
    This is not valid JSON: `{"India":"BAN":"USA"}` – Martin Mar 03 '20 at 11:35
  • 2
    Note that the format you've suggested is not JSON. Specifically, JSON is a key/value format; `{"India":"BAN":"USA"}` is not valid. In JSON that would more naturally be represented as `{"Countries": ["India","BAN","USA"]}` or similar. – Jeroen Mostert Mar 03 '20 at 11:36
  • Sorry yes I need in the format of {"India","BAN","USA"} without Key – Bhushan Mar 03 '20 at 11:51
  • It's basically a string concatenation problem - which is why I've closed as a duplicate - you just need to play around with the parameters a bit - instead of concatenating just `,`, you need to concatenate `","`, and add `{"` to the start and `"}` to the end of your strings. – Zohar Peled Mar 03 '20 at 11:58

1 Answers1

2

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
Salman A
  • 262,204
  • 82
  • 430
  • 521