By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE #tmp(AnalyticGoodsT CHAR(6), date SMALLDATETIME)
INSERT INTO #tmp(AnalyticGoodsT, date)
VALUES ('000001', '20230424'), ('000002', '20230424'), ('000003', '20230424'), ('000004', '20230424')
4 rows affected
SELECT
Date = CONVERT(nvarchar(10), t.date, 104),
bk = JSON_QUERY(N'[' + STRING_AGG(N'"' + STRING_ESCAPE(t.AnalyticGoodsT, 'json') + N'"', N',') + N']')
FROM #tmp t
GROUP BY
t.date
FOR JSON PATH;
| JSON_F52E2B61-18A1-11d1-B105-00805F49916B |
|---|
| [{"Date":"24.04.2023","bk":["000001","000002","000003","000004"]}] |