CODEWITHSUNDEEP
jsonsql-serverstored-procedures
susmita rai
susmita rai

Reputation: 39

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

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.

enter image description here

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

Expected result:

enter image description here

[{"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

Upvotes: 1

Views: 3452

Answers (1)

Əşrəf Cəfərli
Əşrəf Cəfərli

Reputation: 59

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

Upvotes: 2

Related Questions