By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
DROP TABLE IF EXISTS #tmp
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 '["' + STRING_AGG( AnalyticGoodsT, '","') + '"]'
FROM #tmp
group by date
| (No column name) |
|---|
| ["000001","000002","000003","000004"] |
SELECT *
FROM (
SELECT date,
JSON_QUERY('["' + STRING_AGG( AnalyticGoodsT, '","') + '"]') as bk
FROM #tmp
group by date
) AS s
FOR JSON PATH
| JSON_F52E2B61-18A1-11d1-B105-00805F49916B |
|---|
| [{"date":"2023-04-24T00:00:00","bk":["000001","000002","000003","000004"]}] |