By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
DROP TABLE IF EXISTS #TempDestinationTable
DROP TABLE IF EXISTS ##TempResultTable
DECLARE @cols AS NVARCHAR(MAX) = ''
,@query AS NVARCHAR(MAX);
DECLARE @TabRecord NVARCHAR(100)='{"firstName":"Bill","lastName":"Gates","skills":["C#","SQL"]}'
SELECT [key] AS ColumnName
,value
INTO #TempDestinationTable
FROM OPENJSON(@TabRecord);
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.[ColumnName])
FROM #TempDestinationTable c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
DECLARE @TableProperty NVARCHAR(MAX)= REPLACE(@cols, ',', ' NVARCHAR(MAX),')
SET @TableProperty = @TableProperty+' NVARCHAR(MAX)'
DECLARE @TableQuery NVARCHAR(MAX)= 'CREATE TABLE ##TempResultTable ('+@TableProperty+')'
EXEC(@TableQuery)
set @query = 'SELECT ' + @cols + ' from
(
select value, ColumnName
from #TempDestinationTable
) x
pivot
(
max(value)
for ColumnName in (' + @cols + N')
) p '
| firstName | lastName | skills |
|---|---|---|
| Bill | Gates | ["C#","SQL"] |