SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE products_info (
  product_id int NOT NULL,
  out_quantity varchar(4000) DEFAULT '[]',
  in_quantity varchar(4000) DEFAULT '[]'
 );
INSERT INTO products_info VALUES(1, '[{"date":"2022-03-01","quantity":10}, {"date":"2022-03-02","quantity":20}]', '[{"date":"2022-03-15","quantity":30}]');
1 rows affected

SELECT product_id, o.out_date, o.out_quantity, i.in_date, i.in_quantity FROM products_info
CROSS APPLY OPENJSON (
      out_quantity
  ) WITH (
      out_date date '$.date',
      out_quantity int '$.quantity'
  ) o
CROSS APPLY OPENJSON (
      in_quantity
  ) WITH (
      in_date date '$.date',
      in_quantity int '$.quantity'
  ) i;


product_id
out_date
out_quantity
in_date
in_quantity


1
2022-03-01
10
2022-03-15
30

1
2022-03-02
20
2022-03-15
30


CREATE TABLE what_i_want (
  product_id int NOT NULL,
  out_date varchar(255),
  out_quantity varchar(255),
  in_date varchar(255),
  in_quantity varchar(255),
 );
INSERT INTO what_i_want VALUES (1,'2022-03-01','10', NULL, NULL);
INSERT INTO what_i_want VALUES (1,'2022-03-02','20', NULL, NULL);
INSERT INTO what_i_want VALUES (1, NULL, NULL, '2022-03-15','30');
3 rows affected

SELECT * FROM what_i_want;


product_id
out_date
out_quantity
in_date
in_quantity


1
2022-03-01
10
null
null

1
2022-03-02
20
null
null

1
null
null
2022-03-15
30

product_id	out_date	out_quantity	in_date	in_quantity
1	2022-03-01	10	null	null
1	2022-03-02	20	null	null
1	null	null	2022-03-15	30