Create JSON string using variable

Question

I am trying to get following JSON string:

[{

    "Name": "John", 

    "AccountType": 1

},
{

    "Name": "Steven", 

    "AccountType": 1

}
]

I know that AccountType will be always 1 and I have string variable in following format "John;Steven;Brian;Mike"

I was trying to build this JSON using XML PATH and splitToTable function but with no success. How can I achieve it?

Thanks in advance.

Answer 1

In SQL Server 2016 Microsoft added some useful JSON functions. So this can be achieved very easily:

DECLARE @Variable nvarchar(max) = 'John;Steven;Brian;Mike';
SELECT [value] as Name, 1 as AccountType  FROM STRING_SPLIT(@Variable,';')
FOR JSON PATH

More info about this functions here - https://learn.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver15