The MS SQL database has an externally generated non-standard table that generates N pairs of fields and one record at a time. Each pair of field names is divided into two parts separated by underscores, with the first half being the same but unknown, and the second half being fixed name and age.

Now we need to combine the first half of each pair of field names and their corresponding field values into one record, for a total of N records. You can first convert this record from column to row, and then concatenate every two records into one row.

SQL Solution：

SELECT V.id,
       MAX(CASE V.subkey WHEN N'name' THEN OJ.value END) AS name,
       MAX(CASE V.subkey WHEN N'age' THEN OJ.value END) AS age
FROM (SELECT *
      FROM dbo.tb YT
      FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) J(JSON)
     CROSS APPLY OPENJSON (J.JSON) OJ
     CROSS APPLY (VALUES(LEFT(OJ.[key],NULLIF(CHARINDEX('_',OJ.[Key]),0)-1), STUFF(OJ.[Key],1,NULLIF(CHARINDEX('_',OJ.[Key]),0),'')))V(id,subkey)
GROUP BY V.id;
SELECT V.id,
       MAX(CASE V.subkey WHEN N'name' THEN OJ.value END) AS name,
       MAX(CASE V.subkey WHEN N'age' THEN OJ.value END) AS age
FROM (SELECT *
      FROM dbo.tb YT
      FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) J(JSON)
     CROSS APPLY OPENJSON (J.JSON) OJ
     CROSS APPLY (VALUES(LEFT(OJ.[key],NULLIF(CHARINDEX('_',OJ.[Key]),0)-1), STUFF(OJ.[Key],1,NULLIF(CHARINDEX('_',OJ.[Key]),0),'')))V(id,subkey)
GROUP BY V.id;

SQL has the pivot function that can perform column to row conversion, but column names must be written out. Dynamically generating column names would be very complex. Here, we can only change the thinking. First, convert the record into a JSON string, then take multiple "field names: field values" separately, and then use cross join to spell multiple records. The code difficulty is high. The transposition of SQL is very inflexible, so here we use the max... group by method to indirectly implement it, and the code is also a bit verbose.

SPL code is much simpler and easier to understand:

A1： Load data.

A2: Use pivot function to convert column to row, no need to write column names. The new two-dimensional table has 6 records and 2 fields, with field col storing the original field name and row storing the original field value.

A3： Simple implementation of grouping every 2 rows, and the grouping subsets can be retained without aggregation. # is the row number, \ is division to round.

A4： Get values from each group of records by position to form a new two-dimensional table. ~1 represents the first record within the group.

Question source：https://stackoverflow.com/questions/78269917/unpivoting-data-in-sql-from-json