Question 370063 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Unpivoting data in SQL from JSON

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
Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    data

    CREATE TABLE mytable(
   X1AB_name VARCHAR(30) NOT NULL  
  ,X1AB_age  VARCHAR(30)  NOT NULL
  ,Y2AL_name VARCHAR(30) NOT NULL
  ,Y2AL_age  VARCHAR(30)  NOT NULL
);
INSERT INTO mytable
(X1AB_name,X1AB_age,Y2AL_name,Y2AL_age) VALUES 
('Todd','10','Brad','20');

    you can use Pivotand Unpivottogether to get the desired result

    select *
from 
(
select 
PARSENAME(REPLACE(indicatorname,'_','.'),2) 'id',
PARSENAME(REPLACE(indicatorname,'_','.'),1) 'name2',
indicatorvalue
from mytable
unpivot
(
  indicatorvalue
  for indicatorname in ([X1AB_name]
      ,[X1AB_age]
      ,[Y2AL_name]
      ,[Y2AL_age]
      )
) unpiv
) src
pivot
(
  max(indicatorvalue)
  for name2 in ([name], [age])
) piv;

    dbfiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search