How can I convert values of column into array in SQL Server?

Question

I want to convert value of column into an array. But I don't know how. Can anyone help?

Below is the structure of table that I want to change.

[{"entity":"Job","value":"400072 "},{"entity":"Job","value":"400087"}]

Expected result:

[{"entity":"Job","value":[400072, 400087]}]

The code I tried :

SELECT (
   SELECT ose.TaggedEntity AS 'entity', ose.TaggedEntityId AS 'value'
   FROM #OldSharedEntity AS ose
   WHERE ose.TaggedEntityId NOT IN (
      SELECT nse.TaggedEntityId 
      FROM #NewSharedEntity AS nse
   ) 
   FOR JSON PATH, INCLUDE_NULL_VALUES
) AS json

What is your SQL Server version and do you want to generate a JSON array? — Zhorov
– Zhorov, Commented Apr 18, 2020 at 16:19
My SQL Server version is Microsoft SQL Server 2016 (SP2-CU2) (KB4340355) - 13.0.5153.0 (X64). Is it possible?? — susmita rai
– susmita rai, Commented Apr 18, 2020 at 16:23
yes... but i want those value to be in comma separated [a,b,c] not as a string — susmita rai
– susmita rai, Commented Apr 18, 2020 at 16:25
the code convert that into JSON array but i want the value in [a,b,c] format... — susmita rai
– susmita rai, Commented Apr 18, 2020 at 16:27
Thank You @Zhorov I have edited m question and included the code that I tried. — susmita rai
– susmita rai, Commented Apr 18, 2020 at 16:39

Əşrəf Cəfərli · Accepted Answer · 2021-08-31 13:54:02Z

2

If your table's name #yourtable You can try this

   SELECT entity,
      (Select  JSON_QUERY('['+ STRING_AGG(value,',')+']') 
        FROM #yourtable t2 where t2.entity=entity)  value
     FROM #yourtable t 
     GROUP BY entity FOR JSON PATH

answered Aug 31, 2021 at 13:54

Əşrəf Cəfərli

595 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

derekbaker783 Over a year ago

It's worth highlighting that STRING_AGG isn't available on SQL Server 2016 (the version mentioned by the question author in the question comments).

Collectives™ on Stack Overflow

How can I convert values of column into array in SQL Server?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related