Need help to split a long string with multiple substrings containing 4 variables in SQL Server Studio 2016. The string may contain endless amounts of substring within it.
Current state
[{'actual': 0.0, 'budget': 100.00, 'subcat': 'abd', 'fcc_account': '242120'}, {'actual': 200.00, 'budget': 500.00, 'subcat': 'abc', 'fcc_account': '242123'}]
Desired state
| Actual | Budget | Subcat | FCCAccount |
|---|---|---|---|
| 0.00 | 100.00 | abd | 242120 |
| 200.00 | 500.00 | abc | 242123 |
CodePudding user response：
Please find below simple implementation of this -
DECLARE @json NVARCHAR(MAX);
SET @json = '[{"actual": 0.0, "budget": 100.00, "subcat": "abd", "fcc_account": "242120"}, {"actual": 200.00, "budget": 500.00, "subcat": "abc", "fcc_account": "242123"}]'
SELECT *
FROM OPENJSON(@json)
WITH (actual nvarchar(50) , budget nvarchar(50) ,subcat nvarchar(50) ,fcc_account nvarchar(50) )