Turn Cross Apply opensjson results into columns

We have a table that has a field with some json. using cross apply I was able to turn those pairs into columns:

select [mytable].ID,[mytable].[IndexFields],  jsonvalues.[Key], jsonvalues.[Value]

from [mytable]

cross APPLY OPENJSON([mytable].[IndexFields])
WITH
([Key] nvarchar(255) ,
[Value] nvarchar(255) ) as jsonValues
order by [mytable].ID

So how do I turn this into something like :

Thanks.

Answer

If you know the limited set of possible key names in advance, you can just use PIVOT:

;WITH src AS 
(
  select [mytable].ID,[mytable].[IndexFields],
    jsonvalues.[Key], jsonvalues.[Value]
    from dbo.[mytable]
    cross APPLY OPENJSON([mytable].[IndexFields])
    WITH
    ([Key] nvarchar(255) , 
    [Value] nvarchar(255) ) as jsonValues
)
SELECT * FROM src
  PIVOT (
    MAX(Value) FOR [Key] IN 
    ([firstname],[lastname],[MyChoice],[Optional Coverages])
  ) AS p
ORDER BY src.ID;

But you can’t write a PIVOT if you don’t know all those possible values up front. So it’s a wee bit more complicated and requires dynamic SQL (I wrote an article about dynamic PIVOT here):

DECLARE @keys nvarchar(max);

SELECT @keys = STRING_AGG('[' + STRING_ESCAPE([Key], 'json') + ']', ',')
FROM 
(
  SELECT j.[Key]
    FROM dbo.mytable AS t
    CROSS APPLY OPENJSON(t.IndexFields)
    WITH
    (
      [Key]   nvarchar(255),
      [Value] nvarchar(255) 
    ) AS j
    GROUP BY j.[Key]
) AS x;

DECLARE @sql nvarchar(max) = N'
;WITH src AS 
(
  SELECT t.ID, t.IndexFields, j.[Key], j.[Value]
    FROM dbo.mytable AS t
    CROSS APPLY OPENJSON(t.IndexFields)
    WITH
    (
      [Key]   nvarchar(255),
      [Value] nvarchar(255) 
    ) AS j
)
SELECT * FROM src
PIVOT (MAX(Value) FOR [Key] IN (' + @keys + N')) AS p
ORDER BY ID;';

EXEC sys.sp_executesql @sql;

• Example db<>fiddle