Pivot JSON data for multiple rows

Question

Is it possible to pivot OPENJSON data for multiple rows in T-SQL (on SQL Server 2022).

I have a case like below, it works fine for single ID (eg. CustID=111). Surely I can go this route processing each row, but probably there is a way to make it more compact and faster. Even free Copilot doesn't know if this possible

Appreciate your feedback. Please refer to self containing snippet below.

declare @json nvarchar(max);

with t as (
    select 111 CustID, '{ "xNotes": "Not shipped",  "xManager": "Pavel", "xType": "1"}' DataEx
    union all
    select 222 CustID, '{ "xNotes": "Pending",  "xManager": "Maria", "xType": "2"}'
)   
select @json = DataEx from t  -- where Custid = 111;

select [key], [value], [type] --, CustID ???
from openjson(@json);

-- output for single row:::
-- key       value     type
-- xNotes    Pending   1
-- xManager  Maria     1
-- xType     2         1

And this is desired output:

Please do not use images to present data, use table markdown. — Dale K
– Dale K, Commented Nov 23 at 22:24

Dale K · Accepted Answer · 2025-11-24 03:42:58Z

2

Use CROSS APPLY to pass each row to OPENJSON, then use WITH to map JSON paths to column names and datatypes.

WITH t AS (    
  SELECT 111 CustID, '{ "xNotes": "Not shipped",  "xManager": "Pavel", "xType": "1"}' DataEx 
  UNION ALL
  SELECT 222 CustID, '{ "xNotes": "Pending",  "xManager": "Maria", "xType": "2"}'
)     
SELECT
  t.CustID,
  json_data.*
FROM
  t
CROSS APPLY
  OPENJSON(t.DataEx) WITH (
    xNotes     NVARCHAR(32) '$.xNotes',
    xManager   NVARCHAR(32) '$.xManager',
    xType      INT          '$.xType'
  )
    AS json_data
;

CustID	xNotes	xManager	xType
111	Not shipped	Pavel	1
222	Pending	Maria	2

fiddle

edited Nov 24 at 3:42

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

answered Nov 23 at 22:29

MatBailie

87.5k19 gold badges112 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Pivot JSON data for multiple rows

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related