SQL Server 2016

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE t(Id int, typee nvarchar(50), jsonStr nvarchar(max));
INSERT INTO t(Id, typee, jsonStr) VALUES
(3786, 'APV', '{"1":1,"3":3,"4":24,"5":95}'),
(3786, 'VN', '{"1":3,"5":25}'),
(9999, 'VO', '{"1":99,"3":99}');
3 rows affected

-- Expected result
-- {"APV": {"1":1,"3":3,"4":24,"5":95}, "VN":{"1":3,"5":25} }
SELECT Id,(
    SELECT CASE WHEN typee = 'VN'  THEN jsonStr END AS [VN]
         , CASE WHEN typee = 'VO'  THEN jsonStr END AS [VO]
         , CASE WHEN typee = 'APV' THEN jsonStr END AS [APV]
       
    FROM t AS x
    WHERE x.Id = t.Id
    FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER
) AS TEST1

FROM t
GROUP BY Id


Id
TEST1


3786
{"APV":"{"1":1,"3":3,"4":24,"5":95}"},{"VN":"{"1":3,"5":25}"}

9999
{"VO":"{"1":99,"3":99}"}


SELECT Id, (
  SELECT JSON_QUERY(MIN(CASE WHEN typee = 'APV' THEN jsonStr END)) AS [APV]
       , JSON_QUERY(MIN(CASE WHEN typee = 'VN'  THEN jsonStr END)) AS [VN]
       , JSON_QUERY(MIN(CASE WHEN typee = 'VO'  THEN jsonStr END)) AS [VO]
  FROM t AS x
  WHERE x.id = t.id
  FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER
)
FROM t
GROUP BY Id


Id
(No column name)


3786
{"APV":{"1":1,"3":3,"4":24,"5":95},"VN":{"1":3,"5":25}}

9999
{"VO":{"1":99,"3":99}}


Warning: Null value is eliminated by an aggregate or other SET operation.

Id	TEST1
3786	{"APV":"{"1":1,"3":3,"4":24,"5":95}"},{"VN":"{"1":3,"5":25}"}
9999	{"VO":"{"1":99,"3":99}"}

Id	(No column name)
3786	{"APV":{"1":1,"3":3,"4":24,"5":95},"VN":{"1":3,"5":25}}
9999	{"VO":{"1":99,"3":99}}