Unpivoting data in SQL from JSON

Question

This is a follow up to this question: Possible to "unpivot" in SQL when ingested from JSON.

Let's say I have the following SQL table with data that was incorrectly inserted from a JSON document:

X1AB_name     X1AB_age        Y2AL_name       Y2AL_age
"Todd"        10              "Brad"          20

I would like to un-pivot the result based on the prefix (what is before the _ in the column name). Is it possible to do this with dynamic SQL? The answer I want to get is:

 id              name         age
 "X1AB"          "Todd"        10
 "Y2AL"          "Brad"        20

Answer 1

If it's just the 4 columns, or the prefix is static, then this is simply achievable by unpivoting. Though you could use UNPIVOT, like PIVOT, the operator is quite restrictive. You are better off using VALUES to unpivot your data:

SELECT V.id,
       v.name,
       v.age
FROM dbo.YourTable YT
     CROSS APPLY (VALUES(N'X1AB',X1AB_name,X1AB_age),
                        (N'Y2AL',Y2AL_name,Y2AL_age))V(id,name,age);

If the prefixes could be anything, and you effectively have 10's (100's?) of columns and a single row, then you need to do a little more. Though you could use dynamic SQL, I don't actually see the need here. Instead you could actually use FOR JSON to unpivot the data, and then use OPENJSON to help you re-pivot the data. Again, I don't use the (restrictive) PIVOT operator, and use conditional aggregation. So this ends up with something like this:

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.YourTable 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;

The VALUES at the end is used to avoid repetition of the LEFT and STUFF expressions.