SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE person (
    id INT PRIMARY KEY,
    firstname NVARCHAR(50),
    middlename NVARCHAR(50),
    lastname NVARCHAR(50),
    age INT,
    PersonType NVARCHAR(10),
    ModifiedDate DATETIME,
    address NVARCHAR(MAX)
);

INSERT INTO person (id, firstname, lastname, age, address)
VALUES
  (1, 'John', 'Doe', 30, '{"street": "123 Main St", "city": "Springfield", "state": "IL"}')
, (2, 'Jane', 'Doe', 28, '{"street": "456 Elm St", "city": "Shelbyville", "state": "IL"}')
, (3, 'Bob', 'Smith', 35, '{"street": "789 Oak St", "city": "Capital City", "state": "IL"}')
, (4, 'Alice', 'Johnson', 32, '{"street": "246 Maple St", "city": "Ogdenville", "state": "IL"}')
, (5, 'Charlie', 'Brown', 40, '{"street": "369 Pine St", "city": "North Haverbrook", "state": "IL"}')
;
update person
set ModifiedDate = getdate()
;
10 rows affected

select top(10)
  *
from Person
order by 1


id
firstname
middlename
lastname
age
PersonType
ModifiedDate
address


1
John
null
Doe
30
null
2023-05-27 06:00:27.360
{"street": "123 Main St", "city": "Springfield", "state": "IL"}

2
Jane
null
Doe
28
null
2023-05-27 06:00:27.360
{"street": "456 Elm St", "city": "Shelbyville", "state": "IL"}

3
Bob
null
Smith
35
null
2023-05-27 06:00:27.360
{"street": "789 Oak St", "city": "Capital City", "state": "IL"}

4
Alice
null
Johnson
32
null
2023-05-27 06:00:27.360
{"street": "246 Maple St", "city": "Ogdenville", "state": "IL"}

5
Charlie
null
Brown
40
null
2023-05-27 06:00:27.360
{"street": "369 Pine St", "city": "North Haverbrook", "state": "IL"}


Select top(1)
  * 
FROM Person
For JSON Path
       , Without_Array_Wrapper


JSON_F52E2B61-18A1-11d1-B105-00805F49916B


{"id":1,"firstname":"John","lastname":"Doe","age":30,"ModifiedDate":"2023-05-27T06:00:27.360","address":"{"street": "123 Main St", "city": "Springfield", "state": "IL"}"}


select *
from OpenJson ((
  Select top(1)
  * 
  FROM Person
  For JSON Path
         , Without_Array_Wrapper
  ))


key
value
type


id
1
2

firstname
John
1

lastname
Doe
1

age
30
2

ModifiedDate
2023-05-27T06:00:27.360
1

address
{"street": "123 Main St", "city": "Springfield", "state": "IL"}
1


/* key value pairs generated via JSON */

SELECT TOP (25)
      A.[id] AS ID -- the primary key of the source table
    , B.[Key]
    , B.[Value]
FROM Person A
CROSS APPLY (
    SELECT *
    FROM OpenJson((
                /* list out the columns you need to be pivoted into keys here */
                SELECT
                      A.FirstName
                    , A.MiddleName
                    , A.LastName
                    , A.PersonType
                    , A.ModifiedDate
                    , A.Address
                FOR JSON Path
                    , Without_Array_Wrapper
                ))
    ) B
ORDER BY 1, 2


ID
Key
Value


1
Address
{"street": "123 Main St", "city": "Springfield", "state": "IL"}

1
FirstName
John

1
LastName
Doe

1
ModifiedDate
2023-05-27T06:00:27.360

2
Address
{"street": "456 Elm St", "city": "Shelbyville", "state": "IL"}

2
FirstName
Jane

2
LastName
Doe

2
ModifiedDate
2023-05-27T06:00:27.360

3
Address
{"street": "789 Oak St", "city": "Capital City", "state": "IL"}

3
FirstName
Bob

3
LastName
Smith

3
ModifiedDate
2023-05-27T06:00:27.360

4
Address
{"street": "246 Maple St", "city": "Ogdenville", "state": "IL"}

4
FirstName
Alice

4
LastName
Johnson

4
ModifiedDate
2023-05-27T06:00:27.360

5
Address
{"street": "369 Pine St", "city": "North Haverbrook", "state": "IL"}

5
FirstName
Charlie

5
LastName
Brown

5
ModifiedDate
2023-05-27T06:00:27.360


/* key value pairs generated via JSON */

SELECT TOP (25)
      A.[id] AS ID -- the primary key of the source table
    , B.[Key]
    , B.[Value]
FROM Person A
CROSS APPLY (
    SELECT *
    FROM OpenJson((
                SELECT A.*
                FOR JSON Path
                    , Without_Array_Wrapper
                ))
    /*
     * list columns of the source table you do NOT want in the key/value pairs
     */
    WHERE [Key] NOT IN (
              'id' -- the primary key of the source table
            , 'EmailPromotion'
            , 'AdditionalContactInfo'
            , 'Demographics'
            , 'BusinessEntityID'
            )
    ) B
ORDER BY 1, 2


ID
Key
Value


1
ModifiedDate
2023-05-27T06:00:27.360

1
address
{"street": "123 Main St", "city": "Springfield", "state": "IL"}

1
age
30

1
firstname
John

1
lastname
Doe

2
ModifiedDate
2023-05-27T06:00:27.360

2
address
{"street": "456 Elm St", "city": "Shelbyville", "state": "IL"}

2
age
28

2
firstname
Jane

2
lastname
Doe

3
ModifiedDate
2023-05-27T06:00:27.360

3
address
{"street": "789 Oak St", "city": "Capital City", "state": "IL"}

3
age
35

3
firstname
Bob

3
lastname
Smith

4
ModifiedDate
2023-05-27T06:00:27.360

4
address
{"street": "246 Maple St", "city": "Ogdenville", "state": "IL"}

4
age
32

4
firstname
Alice

4
lastname
Johnson

5
ModifiedDate
2023-05-27T06:00:27.360

5
address
{"street": "369 Pine St", "city": "North Haverbrook", "state": "IL"}

5
age
40

5
firstname
Charlie

5
lastname
Brown


select distinct A.id, B.col, cast(B.val as nvarchar(max)) val
from person A
cross apply (
  values 
    ( 'FirstName',   cast(FirstName as sql_variant)),
    ('MiddleName',   cast(MiddleName as sql_variant)),
    ('LastName',     cast(LastName as sql_variant)),
    ('PersonType',   cast(PersonType as sql_variant)),
    ('ModifiedDate', cast(ModifiedDate as sql_variant))
  ) B (col, val)
where B.val is not null


id
col
val


1
FirstName
John

1
LastName
Doe

1
ModifiedDate
May 27 2023  6:00AM

2
FirstName
Jane

2
LastName
Doe

2
ModifiedDate
May 27 2023  6:00AM

3
FirstName
Bob

3
LastName
Smith

3
ModifiedDate
May 27 2023  6:00AM

4
FirstName
Alice

4
LastName
Johnson

4
ModifiedDate
May 27 2023  6:00AM

5
FirstName
Charlie

5
LastName
Brown

5
ModifiedDate
May 27 2023  6:00AM


select distinct A.id, B.col, cast(B.val as nvarchar(max)) val
from person A
cross apply (
  values 
    ( 'FirstName',   cast(FirstName as sql_variant)),
    ('MiddleName',   cast(MiddleName as sql_variant)),
    ('LastName',     cast(LastName as sql_variant)),
    ('PersonType',   cast(PersonType as sql_variant)),
    ('Address',      cast(Address as sql_variant)),
    ('ModifiedDate', cast(ModifiedDate as sql_variant))
  ) B (col, val)
where B.val is not null
Msg 529 Level 16 State 1 Line 9
Explicit conversion from data type nvarchar(max) to sql_variant is not allowed.

id	firstname	middlename	lastname	age	PersonType	ModifiedDate	address
1	John	null	Doe	30	null	2023-05-27 06:00:27.360	{"street": "123 Main St", "city": "Springfield", "state": "IL"}
2	Jane	null	Doe	28	null	2023-05-27 06:00:27.360	{"street": "456 Elm St", "city": "Shelbyville", "state": "IL"}
3	Bob	null	Smith	35	null	2023-05-27 06:00:27.360	{"street": "789 Oak St", "city": "Capital City", "state": "IL"}
4	Alice	null	Johnson	32	null	2023-05-27 06:00:27.360	{"street": "246 Maple St", "city": "Ogdenville", "state": "IL"}
5	Charlie	null	Brown	40	null	2023-05-27 06:00:27.360	{"street": "369 Pine St", "city": "North Haverbrook", "state": "IL"}

key	value	type
id	1	2
firstname	John	1
lastname	Doe	1
age	30	2
ModifiedDate	2023-05-27T06:00:27.360	1
address	{"street": "123 Main St", "city": "Springfield", "state": "IL"}	1