Search code examples
sqljsonsql-serverpivotopen-json

SQL Server JSON Transpose rows to columns

DECLARE @json NVARCHAR(MAX)

SET @json='{"name":"John","surname":"Doe","age":45,"skills":["SQL","C#","MVC"]}';

SELECT *
FROM OPENJSON(@json);

This gives you key, value, and type as Columns.

I want to have Name, surname and age as columns. and the row would be John, Doe, 45.

How do I flip or transpose the columns and rows? I've tried pivot but can't get it to work.

Solution

  • You would use the with clause of openjson():

    select * 
from openjson(@json) with (
    name nvarchar(max), 
    surname nvarchar(max), 
    age int
);

    Demo on DB Fiddle:

    name | surname | age
:--- | :------ | --:
John | Doe     |  45