I have a string column in an SQL-Server table on Azure which contains the following data:
x
{
"status": "success",
"data": [
{
"name": "Jane",
"type": 0
},
{
"name": "John",
"type": 0
}
]
}
How can it be transformed into a comma-separated string containing "Jane, John"
?
Advertisement
Answer
Here how to achieve this via a snippet of some of my older code, you should be able to loop through the table and do this for each row. There may be a quicker way of doing it but this will work.
DECLARE @JSON NVARCHAR(200) = '{"status": "success", "data": [{"name": "Jane", "type": 0},{"name": "John", "type": 0}]}',
@result nvarchar(max) = ''
SELECT @result = @result + [value] + N', '
FROM (
SELECT DISTINCT data.value
FROM OPENJSON(@JSON, '$.data') as jsondata
CROSS APPLY OPENJSON(jsondata.value) as data
WHERE data.[key] = 'name') a
select @result = substring(@result, 1, (LEN(@result)-1))
select @result