By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table Request (
Id int,
RequestId int,
CategoryId int
)
insert into Request (Id,RequestId,CategoryId) values
( 1, 112, 1),
( 2, 123, 1),
( 3, 123, 2);
3 rows affected
SELECT distinct R.RequestId,
(
SELECT
JSON_QUERY('[' + STUFF(( SELECT ',' + '"' + convert(varchar(10), RC.CategoryId) + '"'
FROM Request RC
WHERE RC.RequestId = R.RequestId
FOR XML PATH('')),1,1,'') + ']' ) Categories
FOR JSON PATH , WITHOUT_ARRAY_WRAPPER
) AS Categories
FROM Request R
| RequestId | Categories |
|---|---|
| 112 | {"Categories":["1"]} |
| 123 | {"Categories":["1","2"]} |
SELECT distinct R.RequestId,
JSON_QUERY(
(
SELECT
JSON_QUERY('[' + STUFF(( SELECT ',' + '"' + convert(varchar(10), RC.CategoryId) + '"'
FROM Request RC
WHERE RC.RequestId = R.RequestId
FOR XML PATH('')),1,1,'') + ']' ) Categories
FOR JSON PATH , WITHOUT_ARRAY_WRAPPER
)
, '$.Categories' )
FROM Request R
| RequestId | (No column name) |
|---|---|
| 112 | ["1"] |
| 123 | ["1","2"] |