SQL Server: flatten an array-CodePudding

I have a table that looks like this:

id        my_array
-------------------------
1         ["a", "b", "c"]

I would like to flatten it so it looks like this:

id             my_array
------------------------
1               "a"
1               "b"
1               "c"

I have tried with openjson() without success.

CodePudding user response：

Try this:

SELECT  
      1  id, 
      value my_array 
FROM STRING_SPLIT(replace(replace('["a", "b", "c"]','[',''),']',''),',');

CodePudding user response：

DECLARE @T AS TABLE(id INT,Field VARCHAR(200))


INSERT INTO @T
SELECT 1 ,'["a", "b", "c"]'
UNION
SELECT 2 ,'["x", "y", "z"]'


SELECT A.id,LTRIM(REPLACE(REPLACE(REPLACE(B.value,'"',''),'[',''),']','')) AS my_array
FROM @T A 
CROSS APPLY (SELECT * FROM STRING_SPLIT(Field,',')) B