I have a SQL Server database with a column called Categories containing comma-separated values in the format nvarchar(50). The length of each column is variable from record to record.
I've created a new empty column called CategoriesJSONArray. For all existing records, I need to convert the values in Categories to JSON arrays and write those converted values to CategoriesJSONArray.
How would I accomplish this using T-SQL, or from within the SSMS UI?
EDIT: example, as requested
Current:
Record Categories
1 Sales, Support, Growth
2 Sales, Growth
3 Sales
4 Support, Growth, Sustain
Desired:
Record Categories CategoriesJSONArray
1 Sales, Support, Growth { "Sales" : "Support" : "Growth"}
2 Sales, Growth { "Sales" : "Growth"}
3 Sales { "Sales" }
4 Support, Growth, Sustain { "Sales" : "Growth" : "Sustain"}