CODEWITHSUNDEEP
sqljsonsql-serverexcelt-sql
igy234
igy234

Reputation: 69

Split values from many columns accordingly over multiple rows

Firstly i obatin data from Excel and convert them into DataTable in C# project. Secondly i parse this DataTable into JSON string and send it to database as a stored procedure parameter.

I want to perform the merge operation on some table with values from this JSON string parameter. Values from this parameter can be represented as such table:

DECLARE @JsonData NVARCHAR(MAX);
SET @JsonData = N'[
     {"id": 1, "lval": "-10;15", "hval": "-20;45", "unit": "kg;m"}
     ]';


DECLARE @ExampleTable TABLE (EQ BIGINT, L_VALUE NVARCHAR(100), H_VALUE NVARCHAR(100), UNIT NVARCHAR (30))
INSERT INTO @ExampleTable
SELECT *
FROM OPENJSON(@JsonData) WITH (
[EQ] BIGINT 'strict $.id',
[L_VALUE] NVARCHAR(100) '$.lval',
[H_VALUE] NVARCHAR(100) '$.hval',
[UNIT] NVARCHAR(20) '$.unit')

SELECT * FROM @ExampleTable
EQ (ID) L_VALUE (nvarchar(100)) H_VALUE (nvarchar(100)) UNIT(nvarchar(30))
1 -10;15 -20;45 kg;m

The first value (from either L_VALUE or H_VALUE) before the semicolon stands for the first unit from the UNIT column, second stands for second unit etc... There can be more pairs of values and units or there can be simply one value one unit so in example:

EQ (ID) L_VALUE (nvarchar(100)) H_VALUE (nvarchar(100)) UNIT(nvarchar(30))
1 3;21;-19 2;11;-5 kg;cm;ml

or

EQ (ID) L_VALUE (nvarchar(100)) H_VALUE (nvarchar(100)) UNIT(nvarchar(30))
1 10 -2 cm

I would like to obtain the result in the table form as follows:

EQ (ID FK) L_VALUE (float) H_VALUE (float)) UNIT (nvarchar(30))
1 -10 -20 kg
1 15 45 m

Upvotes: 0

Views: 446

Answers (2)

Zhorov
Zhorov

Reputation: 29943

You may try to transform the values in the L_VALUE, H_VALUE and UNIT columns as JSON (-10;25 into ["-10", "-25"]) and parse the values with additional OPENJSON() call. The result from the second OPENJSON() is a table with columns key, value and type and in case of an array, the key column contains the index of each item in the JSON array, so you need an appropriate JOINs:

Table and JSON:

DECLARE @JsonData NVARCHAR(MAX);
SET @JsonData = N'[
     {"id": 1, "lval": "-10;15", "hval": "-20;45", "unit": "kg;m"},
     {"id": 2, "lval": "-10;15;13", "hval": "-20;45;55", "unit": "kg;m;cm"},
     {"id": 3, "lval": "-10", "hval": "-20", "unit": "kg"}
]';
DECLARE @ExampleTable TABLE (
   EQ BIGINT, 
   L_VALUE NVARCHAR(100), 
   H_VALUE NVARCHAR(100), 
   UNIT NVARCHAR (30)
)

Statement:

INSERT INTO @ExampleTable
SELECT j.[EQ], a.[L_VALUE], a.[H_VALUE], a.[UNIT]
FROM OPENJSON(@JsonData) WITH (
   [EQ] BIGINT 'strict $.id',
   [L_VALUE] NVARCHAR(100) '$.lval',
   [H_VALUE] NVARCHAR(100) '$.hval',
   [UNIT] NVARCHAR(20) '$.unit'
) j
CROSS APPLY (
   SELECT l.[value], h.[value], u.[value]
   FROM OPENJSON(CONCAT('["', REPLACE(j.L_VALUE, ';', '","'), '"]')) l
   JOIN OPENJSON(CONCAT('["', REPLACE(j.H_VALUE, ';', '","'), '"]')) h ON l.[key] = h.[key]
   JOIN OPENJSON(CONCAT('["', REPLACE(j.UNIT, ';', '","'), '"]')) u ON l.[key] = u.[key]
) a (L_VALUE, H_VALUE, UNIT)

Result:

EQ L_VALUE H_VALUE UNIT
----------------------
1  -10     -20     kg
1  15      45      m
2  -10     -20     kg
2  15      45      m
2  13      55      cm
3  -10    -20      kg

Upvotes: 3

Thom A
Thom A

Reputation: 95544

Assuming that there can only be 2 delimited values, and that there are always 2 delimited values, then you could do this:

SELECT JD.EQ,
       V.L_VALUE,
       V.H_VALUE,
       V.UNIT
FROM OPENJSON(@JsonData)
     WITH (EQ bigint '$.id',
           lval varchar(100),
           hval varchar(100),
           unit nvarchar(20)) JD
     CROSS APPLY(VALUES(LEFT(JD.lval,CHARINDEX(';',JD.lval)-1),LEFT(JD.hval,CHARINDEX(';',JD.hval)-1),LEFT(JD.unit,CHARINDEX(';',JD.unit)-1)),
                       (STUFF(JD.lval,1,CHARINDEX(';',JD.lval),''),STUFF(JD.hval,1,CHARINDEX(';',JD.hval),''),STUFF(JD.unit,1,CHARINDEX(';',JD.unit),'')))V(L_VALUE,H_VALUE,UNIT)

I don't use STRING_SPLIT as it does not return the ordinal position, which could result in incorrect values being matched.

Upvotes: 0

Related Questions