How Can I Iterate over each element in JSON array and join with rows in SQL SERVER

Question

I have this table named Employee I want to Pivot this into a new table:

That is into the new table dynamically means the number of attributes of JSON data can be changed dynamically:

How can I do in SQL Server 2017 dynamically?

Answer 1

You can use OPENJSON with a table definition for this.

You only need to use REPLACE to make it valid JSON

SELECT
  e.id,
  e.name,
  j.score,
  j.point
FROM Employee AS e
CROSS APPLY OPENJSON(REPLACE(REPLACE(e.info, 'point', '"point"'), 'score', '"score"'))
  WITH (score int, point int) AS j;